Using SumIfs - can't get the syntax right!

[DaveEade]

So, I'm trying to get some sumifs working based on the values in a column and a row - not sure if it's possible, but anyway - here is what I'm looking at.
There is the data entry sheet
and then two sheets that look for the Resource / Project and date and returns the sum of the values where the match...

See attached photos - the colours show how the links are made.

 

[Alex Blakenburg]

See if this is what you mean

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Resource	Project	1/01/2024	8/01/2024			Project	1/01/2024	8/01/2024		Resource	1/01/2024	8/01/2024
2	Dave	Aries	1	0.8			Aries	1	0.8		Dave	1.5	1
3	Bruce	Gemini	0.5	0.5			Gemini	1	0.7		Bruce	0.5	0.5
4	Dave	Gemini	0.5	0.2			Leo	2	2		Kevin	1	1
5	Kevin	Leo	1	1							Matt	1	1
6	Matt	Leo	1	1
7
Sheet1

Cell Formulas
Range	Formula
H2:I4	H2	=SUMIFS(INDEX($C$2:$D$6,0,MATCH(H$1,$C$1:$D$1,0)),$B$2:$B$6,$G2)
L2:M5	L2	=SUMIFS(INDEX($C$2:$D$6,0,MATCH(L$1,$C$1:$D$1,0)),$A$2:$A$6,$K2)

 

[DaveEade]

Sort of - but the seperate resource and project tables need to be on a separate sheet - the data shown is just an example - in real life the projects are about 20, resources 60 and of course dates are 52 per year

 

[Alex Blakenburg]

Unfortunately when you provide images that don't show column and row references and don't show sheet names, all we can do is provide a generic solution that you will need to modify at your end.
The formulas will transfer correctly to another sheet if you use cut and paste to move them although you will still need to extend the ranges to cover the whole of your data input range and copy it down to all rows and across to all 52 columns.

 

[DaveEade]

Hi sorry about that - I tried adding a minisheet by my laptop wouldn't allow the download of the app!
Thanks though I'm sure I can use whats been given.

 

[Alex Blakenburg]

Let us know if you get stuck. The Index-Match returns the column with the figures for the matching week, the following Criteria and Criteria value filter the Project or Resource to be summed

 

[DaveEade]

Thanks for the help - but I'm getting the dreaded #N/a error when I expand it out - can you see if I've done something obviously wrong?
Thanks

This is the function: =SUMIFS(INDEX($AN$2:$BC$200,0,MATCH(BF$1,$AN$1:$BC$1,0)),$G$2:$G$200,$BE2)

AN2:BC200 is my data so the same as C2:D6 in the above

BF1 is equivalent to L1

AN1:BC1 is the range the same as C1 :D1

G2:G200 is as A2 & A6 and BE2 is K2 in the L2 example.

Any ideas?
Thanks

 

[Alex Blakenburg]

I thought your summary and data were on different sheets but I can't see a sheet reference on the ranges referring to the data input sheet.

 

[DaveEade]

I thought your summary and data were on different sheets but I can't see a sheet reference on the ranges referring to the data input sheet.

Yes, what I'm trying to do is get the formula working on the same sheet - then I can cut and paste on a separate sheet as you suggested earlier on.

Thanks
Dave

 

[Alex Blakenburg]

The formula you have works in principle so that means its likely you have a data issue.
Pick something you know should return a value and then somewhere put using = check that they match exactly ie =G2=BE2 (use whatever rows have the matching value.
Then do the same for the date ie =AP1 = BF1 (pick 2 dates that should match)

Is one of the ranges an Exel Table ? This will convert the date to text and it won't match.

20240419 Sumifs DaveEade.xlsx
	G																																AM	AN	AO	AP	AQ	AR												BD	BE	BF	BG
1	Resource																																	Resource	Project	1/01/2024	8/01/2024														Resource	1/01/2024	8/01/2024
2	Dave																																	Dave	Aries	1	0.8														Dave	1	0.8
3	Bruce																																	Bruce	Gemini	0.5	0.5														Bruce	0.5	0.5
4	Kevin																																	Dave	Gemini	0.5	0.2														Kevin	0.5	0.2
5	Matt																																	Kevin	Leo	1	1														Matt	1	1
6																																		Matt	Leo	1	1
Sheet2

Cell Formulas
Range	Formula
BF2:BG5	BF2	=SUMIFS(INDEX($AN$2:$BC$200,0,MATCH(BF$1,$AN$1:$BC$1,0)),$G$2:$G$200,$BE2)