Sumif?

[greeced]

Here's an example of a spreadsheet.

Column A [] Column B [] Column C
------------------------------------
Gas ----------------------30
--------------Util---------20
--------------Gas ---------50

I've looked far and wide and couldn't figure this out. Lets say I want to Sum up column C only if Gas is in Column A - OR - Column B.

So in the above example the amount should return 80.

Also, should there come a time that Gas is in both columns, I'd only like that line to be counted once, not twice.

Any idea how to do this? I've tried and searched everywhere with no success.

Please help

 

[Richard Schollar]

Hi

One way:

=SUMPRODUCT(--(((A2:A5="Gas")+(B2:B5="Gas"))>0),C2:C5)

 

[adam087]

Which version of XL are you using?
If you've got 07/10 then you can use =SUMIFS()

If you've got 00/03 then you could go for an array formula.

Or a simpler option would be to use Column D as a helper column with the formula =--OR(A2="Gas", B2="Gas") and then use a basic =SUMIF(D:D, 1, C:C) to sum them.

Regards
Adam

 

[greeced]

Hi

One way:

=SUMPRODUCT(--(((A2:A5="Gas")+(B2:B5="Gas"))>0),C2:C5)

This works, thank you.. however one problem.

It seems to only work if I keep the range set at a range that already contains values.

It will return a #N/A if I have the range say a3:a10, but cells a7:a10 are blank. Any ideas?

 

[greeced]

Which version of XL are you using?
If you've got 07/10 then you can use =SUMIFS()

If you've got 00/03 then you could go for an array formula.

Or a simpler option would be to use Column D as a helper column with the formula =--OR(A2="Gas", B2="Gas") and then use a basic =SUMIF(D:D, 1, C:C) to sum them.

Regards
Adam

I do have 2010, but can't seem to get sumifs to work.... I was hoping not to use a helper column if I don't have to..

Thanks for your help guys.

 

[Richard Schollar]

This works, thank you.. however one problem.

It seems to only work if I keep the range set at a range that already contains values.

It will return a #N/A if I have the range say a3:a10, but cells a7:a10 are blank. Any ideas?

It shouldn't do - can you post the exact formula you are using that displays a #N/A. Please copy it directly from the formula bar into your reply.

 

[greeced]

It shouldn't do - can you post the exact formula you are using that displays a #N/A. Please copy it directly from the formula bar into your reply.

I think I figured out the problem... all 3 ranges noted have to be exactly the same in terms of length otherwise it will throw the #N/A at you.

For example:

=SUMPRODUCT(--(((A2:A100="Gas")+(B2:B100="Gas"))>0),C2:C100)

works where:

=SUMPRODUCT(--(((A2:A100="Gas")+(B2:B101="Gas"))>0),C2:C100)

returns the error.

Thanks for your help sir. Much appreciated. I corrected that error and it works great now.

One more thing... I have merged cells in some of my ranges, guess this won't work with merged cells? receiving the #N/A when I use it with the following formula:

=SUMPRODUCT(--(((C21:E100="Gas")+(F21:J100="Gas"))>0),K21:M100)

 

[Richard Schollar]

Merging cells is rarely a good idea. However, you should be able to just use the first column in each merged area:


=SUMPRODUCT(--(((C21:C100="Gas")+(F21:F100="Gas"))>0),K21:K100)

 

[greeced]

Merging cells is rarely a good idea. However, you should be able to just use the first column in each merged area:


=SUMPRODUCT(--(((C21:C100="Gas")+(F21:F100="Gas"))>0),K21:K100)

Wow.. you're great... that worked too!

One final question if I may (thanks for taking all this time)....

Say in column F21:F100 the label "Gas" is text found somewhere in the middle of the cell and is not the only thing within it... is there a way to utilize this to find the word gas in that range and sum as previously done?

 

[Richard Schollar]

You can use this:


=SUMPRODUCT(ISNUMBER(SEARCH("Gas",C21:C100))+ISNUMBER(SEARCH("Gas",F21:F100)),K21:K100)