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:

LOCATION	P01_2018	P02_2018	P03_2018	P04_2018	P05_2016
NO	1	1	1	1	1
USATL	0	0	0	1	0
NO	0	0	0	0	0
NO	1	0	0	1	0
USBAL	1	1	1	1	1
NO	1	1	1	1	1
USLAX	0	1	1	1	0
USNYC	1	0	0	1	1

<tbody>
</tbody>

SUMMARY:

	P01_2018	P02_2018	P03_2018	P04_2018	P05_2016
LANES	5	4	4	7	4
CAT 1	2	2	2	4	2

<tbody>
</tbody>

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.