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

 

[greeced]

You can use this:


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

Excellent.. you sir are an asset to this site.

Thank you

 

[greeced]

You can use this:


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

Sorry my friend, looks like I ran into another problem using this formula.

If "Gas" appeares in colum C and in Column F, it will add the amount in column K twice. I only want to have it sum the row value of K once if "Gas" appears in either of the two other columns.

Your previous formula worked for this, however this one doesn't.

Any ideas?

 

[Aladin Akyurek]

Sorry my friend, looks like I ran into another problem using this formula.

If "Gas" appeares in colum C and in Column F, it will add the amount in column K twice. I only want to have it sum the row value of K once if "Gas" appears in either of the two other columns.

Your previous formula worked for this, however this one doesn't.

Any ideas?

Either...

Just enter:

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

Or...

Control+shift+enter, not just enter:

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