Sumifs formula for multi criteria in multiple column ranges

[Jon3sy]

=SUMIFS('Transaction Raw Data'!$J$3:$J$5009,'Transaction Raw Data'!$G$3:$G$5009,"XYZ",'Transaction Raw Data'!$G$3:$G$5009,"ABC",'Transaction Raw Data'!$H$3:$H$5009,"Operating Expense")

I want to sum column J if it meets the 3 criteria - 2 criteria from column G and one criteria from column H, But my formula is not returning a result
Appreciate your expertise.
Many thanks
Sharon

 

[jtakw]

Hi,

You can't have two different criterion for the same range in SUMIFS, it works like an AND function, so Column G can't be "XYZ" and "ABC" at the same time, you can SUM 2 SUMIFS or use SUMPRODUCT.
I don't have time to create the sheets, so adjust sheet name and ranges to your data:

Excel 2010
	E	F	G	H	I	J
1	9					1
2	9		XYZ	OP		2
3				OP		3
4			XYZ			4
5						5
6						6
7			ABC	OP		7
8				OP		8
9			ABC			9
Sheet4

Cell Formulas
Range	Formula
E1	=SUM(SUMIFS(J1:J9,G1:G9,"XYZ",H1:H9,"OP"),SUMIFS(J1:J9,G1:G9,"ABC",H1:H9,"OP"))
E2	=SUMPRODUCT((G1:G9={"XYZ","ABC"})*(H1:H9="OP")*J1:J9)

 

[Jon3sy]

Absolutely fantastic - thank you ever so much. Works perfectly when you know how and it highlighted that I needed to reference a different column as it included costs that didn't apply. Woohoo!

 

[jtakw]

You're very welcome.

 

[Jon3sy]

I just wondered how I would now turn this formula in to looking at different tabs for the multi criteria sum ifs eg: =SUMIFs(Jayne!$I$2:$I$5001,Jayne!$A$2:$A$5001,"Jayne",Jayne!$D$2:$D$5001,E5,SUMIF(Laurie!$I$2:$I$5001,Laurie!$A$2:$A$5001,"Laurie",Laurie!$D$2:$D$5001,E5) etc - I want to have the formula look at four different tabs for each different person if A2:A5001 = their name. Is that possible? Many thanks

 

[jtakw]

Are we talking about Total SUM of All four tabs for Jayne And Laurie?

Or are you looking for Separate results?

 

[Jon3sy]

Oh apologies - yes I would like a separate total just for that person when their name is in the selected field rather than a total of all four people the formula will reference. Thanks

 

[jtakw]

No problem at All, I was just a little confused between the description and your sample formula..

Looking at your sample formula...

Does Each person have their own tab (say Jayne for example)?
If so, is it still necessary to test Column A for their Name (Jayne) within the Tab Jayne?
Also, what's the criteria in E5?

We can have a formula that's tailored for Each Person, that would mean 4 different formulas for each person and tab...
Or, we can have 1 formula that you can copied down or across for the 4 different persons and tabs with volatile functions.

 

[Jon3sy]

Yes each person has their own tab with their timesheet hours/expenses - the other tab where the formulas are is an invoice template I am creating where they will select their name from a dropdown in cell B2 and then the formula can match their name in that field to their individual tab - so it may not need that extra validation criteria on their name but we will definitely have to tailor the formula for each person as this won't be a formula dragged down into other rows/columns. E5 will be a manual input that will reference an End Of Month date field in each tab so the invoice knows to only show values for that person for that month. Gosh I hope this makes sense. If you need me to post the spreadsheet I will try upload somehow. Thanks

 

[jtakw]

Ok, so I'm skipping the Test for Name in Column A since we're Only extracting from Each Person's individual tab.
But since your Lookup cell for the formula is a "drop down" list, that still means we need a single formula that will point to the correct tab when the selection changes in the drop down.

Assuming the drop down is in A1 (Change A1 to the cell reference of your drop down cell):

Book1
	A	B	C	D	E
1	Jayne	1000
2
3
4
5					5/31/2018
Sheet64

Cell Formulas
Range	Formula
B1	=SUMIFS(INDIRECT(A1&"!I2:I5001"),INDIRECT(A1&"!d2:d5001"),E5)

Book1
	A	B	C	D	E	F	G	H	I
2	Jayne			5/31/2018					1000
3	Jayne			4/30/2018					5
Jayne