Countifs?

[hp75150]

I have the sample sheet below. I need to count how many y's and n's there are for each ad week per store. I tried a few different countifs with no luck. Any help is appreciated.


[TABLE="width: 168"]
<colgroup><col width="22" style="width: 17pt; mso-width-source: userset; mso-width-alt: 804;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="64" style="width: 48pt;" span="2"> <tbody>[TR]
[TD="width: 22, bgcolor: transparent"]1[/TD]
[TD="width: 73, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]Ad Week[/TD]
[TD="bgcolor: transparent"]Store #[/TD]
[TD="bgcolor: transparent"]Y/N[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]1910[/TD]
[TD="bgcolor: transparent"]205[/TD]
[TD="bgcolor: transparent"]y[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]1910[/TD]
[TD="bgcolor: transparent"]205[/TD]
[TD="bgcolor: transparent"]n[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]1910[/TD]
[TD="bgcolor: transparent"]205[/TD]
[TD="bgcolor: transparent"]y[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]1915[/TD]
[TD="bgcolor: transparent"]205[/TD]
[TD="bgcolor: transparent"]n[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]1915[/TD]
[TD="bgcolor: transparent"]205[/TD]
[TD="bgcolor: transparent"]y[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8[/TD]
[TD="bgcolor: transparent"]1915[/TD]
[TD="bgcolor: transparent"]205[/TD]
[TD="bgcolor: transparent"]n[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9[/TD]
[TD="bgcolor: transparent"]1910[/TD]
[TD="bgcolor: transparent"]215[/TD]
[TD="bgcolor: transparent"]n[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"]1910[/TD]
[TD="bgcolor: transparent"]215[/TD]
[TD="bgcolor: transparent"]y[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11[/TD]
[TD="bgcolor: transparent"]1910[/TD]
[TD="bgcolor: transparent"]215[/TD]
[TD="bgcolor: transparent"]n[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]12[/TD]
[TD="bgcolor: transparent"]1915[/TD]
[TD="bgcolor: transparent"]215[/TD]
[TD="bgcolor: transparent"]n[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent"]1915[/TD]
[TD="bgcolor: transparent"]215[/TD]
[TD="bgcolor: transparent"]y[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]14[/TD]
[TD="bgcolor: transparent"]1915[/TD]
[TD="bgcolor: transparent"]215[/TD]
[TD="bgcolor: transparent"]n[/TD]
[/TR]
</tbody>[/TABLE]

 

[tyija1995]

Hey,

In cell D2 type "Yes" and cell E2 type "No" then add the following 2 formulas:

Try in cell D3:
=SUMPRODUCT(($A3=$A$3:$A$14)*($B3=$B$3:$B$14)*($C$3:$C$14="y"))

And then in E3:
=SUMPRODUCT(($A3=$A$3:$A$14)*($B3=$B$3:$B$14)*($C$3:$C$14="n"))

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F
1	Ad Week	Store #	Y/N		1910	205
2	1910	205	y	y	2
3	1910	205	n	n	1
4	1910	205	y
5	1915	205	n
6	1915	205	y
7	1915	205	n
8	1910	215	n
9	1910	215	y
10	1910	215	n
11	1915	215	n
12	1915	215	y
13	1915	215	n
Scratch

Cell Formulas
Range	Formula
E2	=COUNTIFS($A$2:$A$13,$E$1,$B$2:$B$13,$F$1,$C$2:$C$13,$D2)

 

[Marcelo Branco]

Pivot Table?


[TABLE="class: grid"]
<tbody>[TR]
[TD]

Counting of Y/N​

[/TD]
[TD][/TD]
[TD]

Y/N​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Ad Week​

[/TD]
[TD]

Store #​

[/TD]
[TD]

n​

[/TD]
[TD]

y​

[/TD]
[TD]

Grand Total​

[/TD]
[/TR]
[TR]
[TD]

1910​

[/TD]
[TD]

205​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

1910​

[/TD]
[TD]

215​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

1910 Total​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

1915​

[/TD]
[TD]

205​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

1915​

[/TD]
[TD]

215​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

1915 Total​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

2​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Grand Total​

[/TD]
[TD][/TD]
[TD]

7​

[/TD]
[TD]

5​

[/TD]
[TD]

12​

[/TD]
[/TR]
</tbody>[/TABLE]


M.

 

[hp75150]

Thanks