Excel Help and Problems

[AJK]

Unfortunately, SUMIF alone will not work for me, as my "criterion" is not in a single column.

Simplified:



I'd need like four different formulas if I wanted to only sum those cells to the right of "Cost."

e.g. -- =SUMIF(A:A,"cost",B:B)+SUMIF(B:B,"cost",C:C)+SUMIF(C:C,"cost",D:D)+SUMIF(D:D,"cost",E:E)

But given the size of my spreadsheet, that's not going to work.

Seems I need a formula which will search for "Cost" everywhere, and then sum only the cell to the right of that cell.

Any other ideas?

[lechaim2life]

Would you be able to play around with the formatting to make it more organized or is it too much data?

[AJK]

Too much data

[Drago]

This is a messy approach, but you can create a new tab, and create a forumla in each cell to reference your original tab that if A1 (etc) = cost, then give value of cell to the right of it.
Then you'll have a tab full of the amounts only, and you can sum them all up.

[churnbabychurn]

Unfortunately, SUMIF alone will not work for me, as my "criterion" is not in a single column.

Simplified:



I'd need like four different formulas if I wanted to only sum those cells to the right of "Cost."

e.g. -- =SUMIF(A:A,"cost",B:B)+SUMIF(B:B,"cost",C:C)+SUMIF(C:C,"cost",D:D)+SUMIF(D:D,"cost",E:E)

But given the size of my spreadsheet, that's not going to work.

Seems I need a formula which will search for "Cost" everywhere, and then sum only the cell to the right of that cell.

Any other ideas?

Might want to consider using the "match" "index" and sum formulas. Sorry cant think it through now. Good luck

[noturbizniss]

If columns a, c, e, etc say cost and b, D, D, etc have values than you can insert row on top and do sum if on each pair of columns then sum row 1. The alternative would be some kind of vba

[noturbizniss]

If columns a, c, e, etc say cost and b, D, D, etc have values than you can insert row on top and do sum if on each pair of columns then sum row 1. The alternative would be some kind of vba

Just looked at image again you can do that on top of every column

[EJB]

VBA is the way to go if you want anything to be done efficiently. You could make a sluggish array summing all cells to the right of  a cell that matches "Cost" (if that's always the case).

[AnonymousUser]

VBA is the way to go if you want anything to be done efficiently. You could make a sluggish array summing all cells to the right of  a cell that matches "Cost" (if that's always the case).

Depending on the size of the data, it might not be so sluggish.

[AJK]

What would the array look like?

=sum(if(

?

[skyguy918]

Pres Alt+F11 to open the VBA editor. Right-click where it says VBAProject([name of your file]), Insert, Module. Then paste this code in there:

Sub SumCost()

Total = 0

For Each c In Range("B1:E10").Cells
    If c = "Cost" Then
        Total = Total + c.Offset(0, 1).Value
    End If
Next

Range("A1").Value = Total

End Sub

Just replace the the 2 instances of "Range" with the appropriate values for your file. The first should be the range you want to search and the second is where you want excel to paste the sum.

You can set this macro to a hotkey so that you can just click it once on each sheet easily. Back in Excel, click View, Macros, View Macros, select the macro you just created (SumCost), click options, and add a letter in the box after "Ctrl+".

Remember that as written the macro performs the code on the active sheet.

[lubaby]

Pres Alt+F11 to open the VBA editor. Right-click where it says VBAProject([name of your file]), Insert, Module. Then paste this code in there:Just replace the the 2 instances of "Range" with the appropriate values for your file. The first should be the range you want to search and the second is where you want excel to paste the sum.

You can set this macro to a hotkey so that you can just click it once on each sheet easily. Back in Excel, click View, Macros, View Macros, select the macro you just created (SumCost), click options, and add a letter in the box after "Ctrl+".

Remember that as written the macro performs the code on the active sheet.

This looks like you're saving the macro to the Active workbook.
Isn't there a way to save it to the Excel Personal workbook, so the shortcut Ctrl+* is available anytime after?

Edit: Instructions here.

[AJK]

Pres Alt+F11 to open the VBA editor. Right-click where it says VBAProject([name of your file]), Insert, Module. Then paste this code in there:Just replace the the 2 instances of "Range" with the appropriate values for your file. The first should be the range you want to search and the second is where you want excel to paste the sum.

You can set this macro to a hotkey so that you can just click it once on each sheet easily. Back in Excel, click View, Macros, View Macros, select the macro you just created (SumCost), click options, and add a letter in the box after "Ctrl+".

Remember that as written the macro performs the code on the active sheet.

Thank you very much.

Even the thought of VBA scares me... I'll do it if I must, but is there no way to do this with formulas?

[skyguy918]

This looks like you're saving the macro to the Active workbook.
Isn't there a way to save it to the Excel Personal workbook, so the shortcut Ctrl+* is available anytime after?

Edit: Instructions here.

Can't imagine why you'd want to use this particular macro in multiple workbooks, but if you did, your link is a great resource.

Thank you very much.

Even the thought of VBA scares me... I'll do it if I must, but is there no way to do this with formulas?

There is, with array enabled formulas. Not sure which is technically better or more efficient, but you can try this:

=SUM(IF(range="count",OFFSET(range,0,1),0))

Copy the text into a cell, replace the italicized range with the range you want to look in, and instead of pressing enter to save the formula into the cell, press ctrl+shft+enter. This turns it into an array enabled formula.

[noturbizniss]

Thank you very much.

Even the thought of VBA scares me... I'll do it if I must, but is there no way to do this with formulas?

not one formula in one cell. The sheet is kind of messy and excel works best with neatness.

[skyguy918]

not one formula in one cell. The sheet is kind of messy and excel works best with neatness.

I already tested the formula I posted right above you in a simplified version of AJK's sheet, and it works - in one cell.

[AJK]

What'd I do wrong?

[churnbabychurn]

change "count" to cost 

[noturbizniss]

I already tested the formula I posted right above you in a simplified version of AJK's sheet, and it works - in one cell.

Yup, totally forgot about array formulas.

[skyguy918]

change "count" to cost 

Yup, sorry about that.