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):

Class				Total
Revenue	Shoe Sales	Other Revenue	Sock Sales
Expenses	Blah	Blah Blah

<tbody>
</tbody>

and financials like this (simplified example):

Shoe Sales	50
Other Revenue	100
Operating Expense	12
Sock Sales	20
Blah	10
Blah Blah	10

<tbody>
</tbody>

 

[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...

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Class​				Total​		Class​	Value​
2​	Revenue​	Shoe Sales​	Other Revenue​	Sock Sales​	170​		Shoe Sales​	50​
3​	Expenses​	Blah​	Blah Blah​		20​		Other Revenue​	100​
4​							Operating Expense​	12​
5​							Sock Sales​	20​
6​							Blah​	10​
7​							Blah Blah​	10​

<TBODY>
</TBODY>

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))