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

 

[Jon3sy]

No that didn't work - the first part of my original formula worked for Jayne eg: =SUMIFs(Jayne!$I$2:$I$5001,Jayne!$A$2:$A$5001,"Jayne",Jayne!$D$2:$D$5001,E5) - but as soon as I tried to add a formula to calculate Laurie's costs from her tab it didn't work. Each of the 4 separate tabs have exactly the same structure so the sum will always be referenced to the same column but the tab name will be different. I also have to add two other people to the formula. I don't understand how the indirect formula would know which tab to get the data to sum from without the test when a different name is chosen in the dropdown?

 

[jtakw]

What do you mean "No that didn't work", how did it Not work? Did you try the formula...?

The Indirect function within my formula will look at A1 ( Again, change A1 to wherever your drop down for the names are ), then tell the formula which Tab to do the SUM and check the Criteria for E5.
You HAVE to make sure the names in the "drop down" list are EXACTLY as the TAB NAMES, they have to be an EXACT match.

This formula will work whether you have 4 employees & 4 tabs, 40 employees & 40 tabs, 400 employees & 400 tabs, etc., same formula will do ALL.

 

[Jon3sy]

Again my apologies - I must be missing something here through lack of understanding - and I want to attach and image of the screen so have created this url hopefully it works
http://i66.tinypic.com/20gbpkk.jpg - you will see that the 4 x tab names match the dropdown selection and here is the formula I typed in =SUMIFS(INDIRECT(B2&"Laurie Silver!I2:I5001"),INDIRECT(B2&"Laurie Silver!d2:d5001"),E5). or this link http://tinypic.com/r/20gbpkk/9

 

[jtakw]

No, you Do Not need to add the name (Jayne, Laurie, Jon3sy, etc.) in the formula, but I see you have a SPACE in the names, which were not mentioned, just update the formula to this, DON'T add names in the formula:

Book1
	B	C	D	E
2	Laurie Silver	1000
3
4
5				5/31/2018
Sheet64

Cell Formulas
Range	Formula
C2	=SUMIFS(INDIRECT("'"&B2&"'!I2:I5001"),INDIRECT("'"&B2&"'!d2:d5001"),E5)

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

 

[Jon3sy]

Arrgh - that explains it then - thank you soooo much it worked this time - it's helpful when you can see what I'm doing and I now understand the formula. Have a great day.

 

[jtakw]

You're very welcome, glad you got it working.

There's no need to change/adjust the formula as you add employees/tabs, just make sure the Names in the "drop down" list Matches the Tab Names.