SUMIF Question 1

[TimRHSD]

Hi,

I have a column which contains totals. I want to use SUMIF and add columns which have the word 'Total' but do not also contain the word 'Revenue'. Simple data layout is as follows;

Col A Col B
Revenue 1 50 Row 1
Revenue 2 175

Total Revenue 225

Expenses
Salary Category
Salary 10
Hourly 40
Overtime 20
Salary Total 70
Benefits Category
Health 100
Dental 10
Vision 10
Bene Total 120 Row 17

Total Expenses 415 = SUMIF(A1:A17,"*Total*",B1:B17)

What I want is something like,
SUMIF(A1:A17,"Total*" & <> "*Revenue*",B1:B17)

Give me all amounts in column B where column A contains 'Total', but doesn't contain 'Revenue'

Hope that makes sense!

Thanks,

Tim

Tim Rutherford

http://www.Manna-Consulting.com

 

[johnnygeo]

Can you rely on a cell containing both "total" and "revenue" to always contain them together as "total revenue"? If so,

=SUMIF(A1:A17,"*Total*",B1:B17)-SUMIF(A1:A17,"*Total Revenue*",B1:B17)

will work

 

[johnnygeo]

Otherwise, I think you might have to add a column. You could add column C to contain the formula
=IF(ISERR(SEARCH("total",A1)),0,1)*IF(ISERR(SEARCH("revenue",A1)),1,0)

This will evaluate to 1 if the cell contains "total" and not "revenue", 0 for all other combinations.

Then in B18, enter =sumproduct(B1:B17,C1:C17)

 

[TimRHSD]

Thanks Johnny! Your first suggestion worked great. Another one of those 'think outside the box' moments.

Tim

Tim Rutherford

http://www.Manna-Consulting.com