Cleanest Way to Use Sumif adding multiple critera?

[MikeWeaver]

I am looking for the cleanest way to sum everything in one column of a worksheet that meets the criteria for "Revenue" , or "Expense", my revenue column could actually have 10 or 12 line items so I am looking for the cleanest and shortest formula to do this. Thanks so much for any help you can give!

I have my table like this (simplified example):
[TABLE="width: 500"]
<tbody>[TR]
[TD]Class[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD]Shoe Sales[/TD]
[TD]Other Revenue[/TD]
[TD]Sock Sales[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Expenses[/TD]
[TD]Blah[/TD]
[TD]Blah Blah[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


and financials like this (simplified example):[TABLE="width: 500"]
<tbody>[TR]
[TD]Shoe Sales[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Other Revenue[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Operating Expense[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Sock Sales[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Blah[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Blah Blah[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

 

[MikeWeaver]

Or is this a case where sumproduct would be a better option? I am not too familiar with it.

 

[Jonmo1]

Try

=SUM(SUMIF(A1:A100,"*"&{"Revenue","Expense"}&"*",B1:B100))

 

[Daveydoodles]

Thanks Jonmo,

I was never sure how to incorporate wildcards into this style of formula.

 

[Jonmo1]

You're welcome.

 

[MikeWeaver]

Hi, maybe I am mistaken, but wouldn't this formula sum everything with "revenue" or "expense" in the title? I am looking for a formula that would only sum the three classifiers for revenue in my table (which includes things like "sock sales" that don't have the word revenue in them). I am also looking to seperate revenue and expenses, not combine them. Thanks for your help, and sorry if I was unclear!

 

[Jonmo1]

I thought that was what you wanted?

Are you saying you want seperate formulas, 1 for revenue, another for expenses etc?

Then it's just
=SUMIF(A1:A100,"*Revenue*",B1:B100)

If you want to use a cell reference for Revenue, try
=SUMIF(A1:A100,"*"&D1&""*,B1:B100)
D1=Revenue

 

[MikeWeaver]

Sorry, I don't think I am communicating this very well. Maybe it would help to just focus on the Revenue total. I am looking for it to sum "Shoe Sales", "Sock Sales" and "Other Revenue", these line items could be anything, so I don't want it to just look for search for terms with "sales" or "revenue" in the name. I believe your formula would only find "Other Revenue".

Thank you for your help and I apologize again for the lack of clarity in my request.

 

[Marcelo Branco]

Maybe something like this...


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Class​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Total​

[/TD]
[TD][/TD]
[TD]

Class​

[/TD]
[TD]

Value​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Revenue​

[/TD]
[TD]

Shoe Sales​

[/TD]
[TD]

Other Revenue​

[/TD]
[TD]

Sock Sales​

[/TD]
[TD]

170​

[/TD]
[TD][/TD]
[TD]

Shoe Sales​

[/TD]
[TD]

50​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Expenses​

[/TD]
[TD]

Blah​

[/TD]
[TD]

Blah Blah​

[/TD]
[TD][/TD]
[TD]

20​

[/TD]
[TD][/TD]
[TD]

Other Revenue​

[/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Operating Expense​

[/TD]
[TD]

12​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Sock Sales​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Blah​

[/TD]
[TD]

10​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Blah Blah​

[/TD]
[TD]

10​

[/TD]
[/TR]
</TBODY>[/TABLE]


Formula in E2 copied down
=SUMPRODUCT(SUMIF($G$2:$G$100,B2:D2,$H$2:$H$100))

Hope this helps

M.

 

[Jonmo1]

Oh, Ok.

Then for Revenue it would be
=SUM(SUMIF(A1:A100,{"Sock Sales","Shoe Sales","Other Revenue"},B1:B100))

and for Expenses
=SUM(SUMIF(A1:A100,{"Expenses","Blah","Blah Blah"},B1:B100))

To use a Range for the criteria, say E2:G2
Try
=SUMPRODUCT(SUMIF(A1:A100,E2:G2,B1:B100))