Countifs with Mutiple Criteria

[adrienne0914]

This seems like it would be a simple formula but I keep getting a #Value error when I try it. Trying to get a count based on criteria in Column A in addition to Row 1:

DATA:
[TABLE="width: 500"]
<tbody>[TR]
[TD]LOCATION[/TD]
[TD]P01_2018[/TD]
[TD]P02_2018[/TD]
[TD]P03_2018[/TD]
[TD]P04_2018[/TD]
[TD]P05_2016[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]USATL[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD]USBAL[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]USLAX[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD]USNYC[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]


SUMMARY:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]P01_2018[/TD]
[TD]P02_2018[/TD]
[TD]P03_2018[/TD]
[TD]P04_2018[/TD]
[TD]P05_2016[/TD]
[/TR]
[TR]
[TD]LANES[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD]CAT 1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

I want the first line of the summary to count all of the 1s from each column. I want the second line to only count the 1's if Column A does not equal no AND heading matches. Expect results in summary above. I know I could do a pivot table to get a summary, but I have a monthly report that will be pulling this and data from others into one report. So a pivot table is not possible.

 

[jtakw]

Hi,

Will this work for you?

Book1
	A	B	C	D	E	F
1	DATA:
2	LOCATION	P01_2018	P02_2018	P03_2018	P04_2018	P05_2016
3	NO	1	1	1	1	1
4	USATL	0	0	0	1	0
5	NO	0	0	0	0	0
6	NO	1	0	0	1	0
7	USBAL	1	1	1	1	1
8	NO	1	1	1	1	1
9	USLAX	0	1	1	1	0
10	USNYC	1	0	0	1	1
11
12	SUMMARY:
13		P01_2018	P02_2018	P03_2018	P04_2018	P05_2016
14	LANES	5	4	4	7	4
15	CAT 1	2	2	2	4	2
Sheet35

Cell Formulas
Range	Formula
B14	=SUMPRODUCT(($B2:$F2=B13)*($B3:$F10=1))
B15	=SUMPRODUCT(($B2:$F2=B13)*($B3:$F10=1)*($A3:$A10<>"No"))

Formulas copied across.

 

[adrienne0914]

Hi,

Will this work for you?

Sorry for the delayed response. I had to wait until I got back to work.

jtakw, it worked like a charm. THANK YOU!!

 

[jtakw]

You're welcome.