SUMIFS with multiples conditions on rows and columns

[rkaczano]

I am trying to do a SUMIF on a data table and I need to use criteria for the row data in the first two columns but also criteria for the column header data. I cannot seem to get this to work.

Example: assume in the table I have years (2010 to 2015) and month values (Jan to Dec) in columns A and B. Then assume I have volume data for a variety of products in columns C to F. And the product names are in the column headers for columns C and F.

If I know in advance that column C has data for "product1" and I wanted volume from 2010 February I would do SUMIFS(C2:C72, A2:A72,"2010", F2:F73,"February").

But I don't know exactly which column has the data for product1 but I do know that it is between columns C and F.

I am trying to widen the "sum range" which is the first term in the SUMIFS as follows: SUMIFS(C2:F72, A2:A72,"2010", F2:F73,"February", A1:F1, "product1"). But this is returning an error. I am effectively trying to use a SUMIF with multiple conditions but two of the conditions pertain to rows and one of the conditions pertains to columns.

There must be a way to do this - like using Index/Match logic but to return an array of results within a SUMIF

Thanks

 

[awoohaw]

is this something that could work for you? If something is not set up correctly, then please use the xl2bb add in and give a small sample copy of your data.

Some Mr Excel Questions.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1			Year:	2010
2			Month	Oct
3			Product	Prod1
4
5			Total:	812
6
7		Year	Month	Prod1	Prod2	Prod3		Years	Months	Product
8		2010	Oct	478	316	401		2010	Apr	Prod1
9		2010	Oct	334	407	459		2011	Feb	Prod2
10		2010	Nov	318	422	423		2012	Jan	Prod3
11		2011	Jan	426	479	339			Jun
12		2011	Feb	337	307	458			Mar
13		2011	Feb	363	459	457			May
14		2012	Mar	304	350	476			Nov
15		2012	Apr	420	332	499			Oct
16		2012	May	391	464	325
17		2012	Jun	322	490	425
18
19
Sheet3

Cell Formulas
Range	Formula
D5	D5	=SUM((D1&D2=B8:B17&C8:C17)*CHOOSE(MATCH(D3,D7:F7,0),D8:D17,E8:E17,F8:F17))

Cells with Data Validation
Cell	Allow	Criteria
D1	List	=Years
D2	List	=Months
D3	List	=Product

 

[AhoyNC]

Maybe use SUMPRODUCT

Book1
	A	B	C	D	E	F
1	Year	Month	Prod4	Prod2	Prod1	Prod3
2	2021	Jan	189	729	545	747
3	2021	Feb	497	191	104	321
4	2022	Jan	656	178	451	129
5	2022	Feb	766	246	118	793
6
7
8		Year	2022
9		Month	Feb
10		Prod	Prod1
11		Volume	118
Sheet4

Cell Formulas
Range	Formula
C11	C11	=SUMPRODUCT(($A$2:$A$5=C8)*--($B$2:$B$5=C9)*--($C$1:$F$1=$C$10)*($C$2:$F$5))

 

[Fluff]

Another option

=SUMIFS(INDEX(C2:C72,,MATCH("Product1",C1:F1,0)),A2:A72,2010,B2:B72,"Feb")