count function

[omer1234]

Given this data, how do I count how many times contamination had a "1"

Team A - = 1 time
Team D - 2 times
Team C - 1 times
Team Z - 0 times

Would be nice if the formula can ignore all the "0" or blank cells.

Thanks

***** if I were to count Wrinkles with a 1 in a cell , only Team z would have 1 entry ***********

	Team D	Team C	Team A	Team D	Team C	Team Z
Column A	Column B	Column C	Column D	Column E	Column F	Column G
Wrinkles	0	3	0	3	3	1
Contamination	1	1	1	1	0	0
Holes	3	0	1	3	3	0
Wrap	0	1	3	3	1	0

 

[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’)

Try this

24 02 28.xlsm
	A	B	C	D	E	F	G
1		Team D	Team C	Team A	Team D	Team C	Team Z
2	Column A	Column B	Column C	Column D	Column E	Column F	Column G
3	Wrinkles	0	3	0	3	3	1
4	Contamination	1	1	1	1	0	0
5	Holes	3	0	1	3	3	0
6	Wrap	0	1	3	3	1	0
7
8		Contamination	Wrinkles
9	Team A	1	0
10	Team C	1	0
11	Team D	2	0
12	Team Z	0	1
Count

Cell Formulas
Range	Formula
B9:C12	B9	=SUMPRODUCT(($B$1:$G$1=$A9)*($A$3:$A$6=B$8)*($B$3:$G$6=1))

 

[omer1234]

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’)

Try this

24 02 28.xlsm
	A	B	C	D	E	F	G
1		Team D	Team C	Team A	Team D	Team C	Team Z
2	Column A	Column B	Column C	Column D	Column E	Column F	Column G
3	Wrinkles	0	3	0	3	3	1
4	Contamination	1	1	1	1	0	0
5	Holes	3	0	1	3	3	0
6	Wrap	0	1	3	3	1	0
7
8		Contamination	Wrinkles
9	Team A	1	0
10	Team C	1	0
11	Team D	2	0
12	Team Z	0	1
Count

Cell Formulas
Range	Formula
B9:C12	B9	=SUMPRODUCT(($B$1:$G$1=$A9)*($A$3:$A$6=B$8)*($B$3:$G$6=1))

I am getting #N/A error , can you verify formula please

 

[Peter_SSs]

Could you please address the first point in my previous post?

I am getting #N/A error , can you verify formula please

I have provided my sample data with the formula showing as working. You can copy my sheet using this icon at the top left of my mini sheet and paste into cell A1 of a blank worksheet to test.

Perhaps you could provide your sample data also with XL2BB and then we could be more sure we are testing with the right data and layout.

 

[omer1234]

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’)

Try this

24 02 28.xlsm
	A	B	C	D	E	F	G
1		Team D	Team C	Team A	Team D	Team C	Team Z
2	Column A	Column B	Column C	Column D	Column E	Column F	Column G
3	Wrinkles	0	3	0	3	3	1
4	Contamination	1	1	1	1	0	0
5	Holes	3	0	1	3	3	0
6	Wrap	0	1	3	3	1	0
7
8		Contamination	Wrinkles
9	Team A	1	0
10	Team C	1	0
11	Team D	2	0
12	Team Z	0	1
Count

Cell Formulas
Range	Formula
B9:C12	B9	=SUMPRODUCT(($B$1:$G$1=$A9)*($A$3:$A$6=B$8)*($B$3:$G$6=1))

I am using office 365 & I will update my profile

Thx

 

[Peter_SSs]

I am using office 365 & I will update my profile

Great. That just leaves you to test my data/formulas directly and/or provide your data/layout with XL2BB

 

[omer1234]

Great. That just leaves you to test my data/formulas directly and/or provide your data/layout with XL2BB

Mine is formatted a little different but I still dont understand why I am getting #N/A

=SUMPRODUCT((B7:AP7=$A2)*(A8:A58=C$1)*(B8:AP33=1))

		Lamination
A-Team		#N/A
Team C
D-Team
Team Z
Team	A-Team	D-Team	A-Team	D-Team	D-Team	D-Team	D-Team	D-Team	A-Team
Lamination	1​	1​	0​	0​	0​	0​	0.00​	0.00​	0.00​
Tail Seal	0​	0​	0​	0​	0​	0​	0.00​	0.00​	0.00​
Roll Start	0​	0​	0​	0​	0​	0​	0.00​	0.00​	0.00​
Contamination	0​	0​	0​	0​	0​	0​	0.00​	0.00​	0.00​
Wrinkles	1.00​	0.00​	0.00​	0.00​	0.00​	0.00​	0.00​	0.00​	0.00​
Saw Cut or Slitter	0.00​	0.00​	0.00​	0.00​	0.00​	0.00​	0.00​	0.00​	0.00​

 

[Peter_SSs]

Mine is formatted a little different but I still dont understand why I am getting #N/A

=SUMPRODUCT((B7:AP7=$A2)*(A8:A58=C$1)*(B8:AP33=1))

Because you haven't matched your range sizes. Those two red numbers should be the same as each other.

 

[Fluff]

Duplicate to: Count function help

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.