I'm stuck on an issue I need resolved quickly.. wondering if any of you could help me.
Here's the basic sheet:
A | B | C |
---------------------
|Gas | | 20 |
| | New Gas | 50 |
Gas | New Gas | 30 |
What I need is a formulation of Sumproduct or sumif that will sum Range C based on the word Gas.
Using formula:
=SUMPRODUCT(ISNUMBER(SEARCH("Gas",A1:A3))+ISNUMBER(SEARCH("Gas",B1:B3)),C1:C3)
works somewhat, but it will sum the value in row 3 twice (because gas appears twice), which is not what I want. I only want it to sum once per row regardless of how many times the word Gas appears.
Using formula:
=SUMPRODUCT(--((($A$1:$A$3="Gas")+($B$1:$B$3="Gas"))>0),$C$1:$C$3)
doesn't work properly, because in column B its listed as "New Gas", and this formula won't find the word gas within the cell.
What I need is a combination of the two. Find instances of gas in a row, then sum up the range. However if Gas appears twice in the two ranges of a specific row, I want the numerical figure to only be summed up once.
Any idea how to do this? How do I make the summed value show up as 100 because "Gas" appears in the 3 rows, regardless of the text position of "gas" within the specific cell?
Thank you.
Here's the basic sheet:
A | B | C |
---------------------
|Gas | | 20 |
| | New Gas | 50 |
Gas | New Gas | 30 |
What I need is a formulation of Sumproduct or sumif that will sum Range C based on the word Gas.
Using formula:
=SUMPRODUCT(ISNUMBER(SEARCH("Gas",A1:A3))+ISNUMBER(SEARCH("Gas",B1:B3)),C1:C3)
works somewhat, but it will sum the value in row 3 twice (because gas appears twice), which is not what I want. I only want it to sum once per row regardless of how many times the word Gas appears.
Using formula:
=SUMPRODUCT(--((($A$1:$A$3="Gas")+($B$1:$B$3="Gas"))>0),$C$1:$C$3)
doesn't work properly, because in column B its listed as "New Gas", and this formula won't find the word gas within the cell.
What I need is a combination of the two. Find instances of gas in a row, then sum up the range. However if Gas appears twice in the two ranges of a specific row, I want the numerical figure to only be summed up once.
Any idea how to do this? How do I make the summed value show up as 100 because "Gas" appears in the 3 rows, regardless of the text position of "gas" within the specific cell?
Thank you.
