Count cell in multiple columns based on criteria found in other multiple columns

[randomXP]

Hello all,

I am having a problem, I thought I got it all figured out with sumproduct function. However, it doesn't work as it should. I am trying to count all the cells containg "2" in columns AM:AO, but only if "1" is found in columns AJ:AL. Ideally a formula that can be further filtered, for example on first row header. So, to summarize, for columns where Zone 1 is found in header and code 1, count all cells with code 2 in columns where Zone 2 is found. Hope I am making myself clear and thanks to all for helping me!

Work_Carr4_traseu_v1.xlsx
	AJ	AK	AL	AM	AN	AO
1	Zone 1:A	Zone 1:B	Zone 1:C	Zone 2: A	Zone 2: B	Zone 2: C
2	1			2
3	1			2
4	1			2
5	1			2
6	1			2
7	1			3
8	1			3
9	1			3
10	1	1		4
11	1			5
12	1			6
13	1	1		6
14	1			7
15	1			9
16	1		1	13
17	1
18	1
19	1
20	1
21	1				2
22	1
23	1
24	1				2
25	1				2
26	1				2
rec

 

[DanteAmor]

Do you need this?

Dante Amor
	AJ	AK	AL	AM	AN	AO	AP
1	Zone 1:A	Zone 1:B	Zone 1:C	Zone 2: A	Zone 2: B	Zone 2: C	6
2	1			2
3	1			2
4	1			2
5	1			2
6	1			2
7	1			3
8	1			3
9	1			3
10	1	1		4
11	1			5
12	1			6
13	1	1		6
14	1			7
15	1			9
16	1		1	13
17	1
18	1
19	1
20	1
21	1	1			2
22	1
23	1
24	1				2
25	1				2
26	1				2
t

Cell Formulas
Range	Formula
AP1	AP1	=COUNTIFS(AJ:AJ,1,AM:AM,2)+COUNTIFS(AK:AK,1,AN:AN,2)+COUNTIFS(AL:AL,1,AO:AO,2)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)