sum column a if column b contains specific text

[Luigi802]

So I don't think there is a single formula to sum certain columns that contain specific text if that text is in a cell with other text, for example

[TABLE="width: 193"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]$657.34[/TD]
[TD]XX Oil company 234[/TD]
[/TR]
[TR]
[TD]$34.20[/TD]
[TD]Gas[/TD]
[/TR]
[TR]
[TD]$432.98[/TD]
[TD]Car payment[/TD]
[/TR]
[TR]
[TD]$653.97[/TD]
[TD]Oil supplier[/TD]
[/TR]
[TR]
[TD]$400.00[/TD]
[TD]Oil[/TD]
[/TR]
[TR]
[TD]$56.50[/TD]
[TD]Gas[/TD]
[/TR]
[TR]
[TD]$678.76[/TD]
[TD]XX Oil company 236[/TD]
[/TR]
</tbody>[/TABLE]

I want to sum the amount in column A based on which ever cells in column B contain the word oil, easy to do if the rest of the text in the cell is the same but not sure how to do this simply with other different text in the same cells with the word oil.

 

[yesterdays]

OK try this:

=SUM(IF(ISERROR(FIND("Oil",B1:B7))=FALSE,1,0)*A1:A7)

Confirm with Ctrl Shift Enter

 

[vlady]

=sumif (B1:B10,"*oil*",A1:A10)

if you use cell ref.
say C1 contains "oil"


=sumif (B1:B10,"*"&C1&"*",A1:A10)

*oil -> xxxoil
*oil* -> xxxoilxxx

 

[Luigi802]

OK try this:

=SUM(IF(ISERROR(FIND("Oil",B1:B7))=FALSE,1,0)*A1:A7)

Confirm with Ctrl Shift Enter

Awesome that worked, it's case sensitive but that's ok it still works, Thank you!

 

[Luigi802]

=sumif (B1:B10,"*oil*",A1:A10)

if you use cell ref.
say C1 contains "oil"


=sumif (B1:B10,"*"&C1&"*",A1:A10)

*oil -> xxxoil
*oil* -> xxxoilxxx

Yes this works fin and I've been using this but I want to eliminate the extra column