Sum values based on multiple column criteria and a row criteria

[dunlop407703]

Hi

I would like to find a formula which will allow me to sum a set of data value based on meeting a couple of column criteria and 1 row criteria. A snapshot example of the data is below.

What I need to do is sum all values in the range C2:J7 where A2:A7=Criteria1, B2:B7=Criteria2 and C1:J1=Criteria3.

I cannot use sumproduct((A2:A7=Criteria1)*(B2:B7=Criteria2)*(C1:J1=Criteria3),C2:J7) as the number of columns searched is different to the number of rows.

Any possible solutions would be very appreciated.

	A	B	C	D	E	F	G	H	I	J
1	Code	Lvl	2017\Apr	2017\May	2017\Jun	2017\Jul	2017\Aug	2017\Sep	2017\Oct	2017\Nov
2	WU	Main	0	0	0	100	200	0	0	0
3	WI	Main	0	0	0	0	100	300	0	0
4	WU	South	200	100	50	400	50	0	0	0
5	WI	South	0	0	0	200	100	200	0	100
6	LX	South	0	100	200	200	0	0	0	0
7	MN1	South	0	0	0	100	200	50	0	0

<tbody>
</tbody>

 

[Weazel]

your sumproduct formula should work just fine the way you have it written

 

[JLGWhiz]

This worked for me

=(SUMIF(A2:A7,">0")+SUMIF(B2:B7,">1")+SUMIF(C1:H1, ">2"))