Multiple column sum_range for sumifs

[JCCarter]

I'm trying to sum several columns based on row and column criteria. I cannot figure out what I'm doing wrong. Below is a small portion of my spreadsheet.
My formula is =SUMIFS($AA$5:$AE$8, $Z$5:$Z$8,$Z5,$AA$4:$AE$4,AA$4)

column/row	Z	AA	AB	AC	AD	AE
4	Task	SP	SS	SP	A5	S3
5	Task 1	7.25​	40​	10​	15​	8​
6	Task 1	13.25​	80​	12​	25​	15​
7	Task 2	0​	0​	0​	0​	0​
8	Task 3	0​	0​	0​	0​	0​

 

[AhoyNC]

Your ranges need to be of the same size to use SUMIFS.
Since you have Excel 365 try the FILTER function with SUM.

Book1
	Y	Z	AA	AB	AC	AD	AE
4		Task	SP	SS	SP	A5	S3
5		Task 1	7.25	40	10	15	8
6		Task 1	13.25	80	12	25	15
7		Task 2	0	0	0	0	0
8		Task 3	0	0	0	0	0
9
10	Sum	Task 1	225.5
Sheet1

Cell Formulas
Range	Formula
AA10	AA10	=SUM(FILTER($AA$5:$AE$8,$Z$5:$Z$8=$Z$10,""))

 

[jasonb75]

From your example it is not entirely clear what you are trying to do, the criteria for row and column don't appear to serve any purpose, to give you something to work with SUMIFS needs all ranges to be the same size, for what you are trying to do you will need to use SUMPRODUCT instead.

=SUMPRODUCT($AA$5:$AE$8*($Z$5:$Z$8=$Z5)*($AA$4:$AE$4=AA$4))

Alternatively, as you are only using equal criteria you could use INDEX and MATCH instead

=INDEX($AA$5:$AE$8,MATCH($Z5,$Z$5:$Z$8,0),MATCH(AA$4,$AA$4:$AE$4,0))

 

[JCCarter]

Your ranges need to be of the same size to use SUMIFS.
Since you have Excel 365 try the FILTER function with SUM.

Book1
	Y	Z	AA	AB	AC	AD	AE
4		Task	SP	SS	SP	A5	S3
5		Task 1	7.25	40	10	15	8
6		Task 1	13.25	80	12	25	15
7		Task 2	0	0	0	0	0
8		Task 3	0	0	0	0	0
9
10	Sum	Task 1	225.5
Sheet1

Cell Formulas
Range	Formula
AA10	AA10	=SUM(FILTER($AA$5:$AE$8,$Z$5:$Z$8=$Z$10,""))

I'm trying to sum all the hours for each task and each labor category (SP, SS, etc.). Since there are more than one row with the same task and more than one column with the same labor category, I cannot use a pivot table without rearranging the data, which is not possible. This is what I'm trying to accomplish

HOURS
Labor Cat	Task 1	Task 2	Task 3
A5	40.00​	0.00​	0.00​
S3	23.00​	0.00​	0.00​
SP	42.50​	0.00​	0.00​
SS	120.00​	0.00​	0.00​
Autofill	0.00​	0.00​	0.00​
Grand Total	225.50​	0.00​	0.00​

 

[JCCarter]

=SUMPRODUCT($AA$5:$AE$8*($Z$5:$Z$8=$Z5)*($AA$4:$AE$4=AA$4))

Thank you. I believe this works for me.

 

[JCCarter]

Thank you. I believe this works for me.

How would you eliminate the values that are the same (i.e., two SPs and two Task 1s) when trying to make a summary table?

 

[jasonb75]

Something like this maybe?

gtd562 CountIF (version 1).xlsb
	A	B	C	D	E	F	G
1	Task	SP	SS	SP	A5	S3
2	Task 1	7.25	40	10	15	8
3	Task 1	13.25	80	12	25	15
4	Task 2	0	0	0	0	0
5	Task 3	0	0	0	0	0
6
7		Task 1	Task 2	Task 3
8	SP	42.5	0	0
9	SS	120	0	0
10	A5	40	0	0
11	S3	23	0	0
12
13	total	225.5	0	0
Sheet5

Cell Formulas
Range	Formula
B7:E7	B7	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$5)/ISNA(MATCH($A$2:$A$5,$A$7:A$7,0)),1)),"")
A8:A12	A8	=IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN($B$1:$F$1)/ISNA(MATCH($B$1:$F$1,$A$7:$A7,0)),1)),"")
B8:E12	B8	=IF(OR(B$7="",$A8=""),"",SUMPRODUCT($B$2:$F$5,($B$1:$F$1=$A8)*($A$2:$A$5=B$7)))
B13:D13	B13	=SUM(B8:B11)

 

[JCCarter]

Something like this maybe?

gtd562 CountIF (version 1).xlsb
	A	B	C	D	E	F	G
1	Task	SP	SS	SP	A5	S3
2	Task 1	7.25	40	10	15	8
3	Task 1	13.25	80	12	25	15
4	Task 2	0	0	0	0	0
5	Task 3	0	0	0	0	0
6
7		Task 1	Task 2	Task 3
8	SP	42.5	0	0
9	SS	120	0	0
10	A5	40	0	0
11	S3	23	0	0
12
13	total	225.5	0	0
Sheet5

Cell Formulas
Range	Formula
B7:E7	B7	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$5)/ISNA(MATCH($A$2:$A$5,$A$7:A$7,0)),1)),"")
A8:A12	A8	=IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN($B$1:$F$1)/ISNA(MATCH($B$1:$F$1,$A$7:$A7,0)),1)),"")
B8:E12	B8	=IF(OR(B$7="",$A8=""),"",SUMPRODUCT($B$2:$F$5,($B$1:$F$1=$A8)*($A$2:$A$5=B$7)))
B13:D13	B13	=SUM(B8:B11)

Took me a while to get the formulas adjusted to the right data, but thank you sooo much!