How to get unique count (frequency) for a values with two criteria

[Boubiche]

[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 64"]Count[/TD]
[TD="class: xl67, width: 64"]Repeat[/TD]
[TD="class: xl68, width: 64"]Emp #[/TD]
[TD="class: xl68, width: 64"]CSR [/TD]
[TD="class: xl68, width: 64"]TM [/TD]
[TD="class: xl68, width: 64"]Group[/TD]
[TD="class: xl68, width: 64"]Presence[/TD]
[TD="class: xl68, width: 64"]Schedule[/TD]
[TD="class: xl68, width: 64"]Note[/TD]
[TD="class: xl68, width: 64"]Year[/TD]
[/TR]
[TR]
[TD="class: xl69, align: right"]1[/TD]
[TD="class: xl70"]Yes[/TD]
[TD="class: xl71"]3133930[/TD]
[TD="class: xl72"]Nancy[/TD]
[TD="class: xl72"]Gab [/TD]
[TD="class: xl70"]FR[/TD]
[TD="class: xl70"]Yes[/TD]
[TD="class: xl66"]Day[/TD]
[TD="class: xl70"]N/A[/TD]
[TD="class: xl66"]2016[/TD]
[/TR]
[TR]
[TD="class: xl69, align: right"]2[/TD]
[TD="class: xl70"]No[/TD]
[TD="class: xl71"]45490[/TD]
[TD="class: xl72"]Angela[/TD]
[TD="class: xl72"]MArvin[/TD]
[TD="class: xl70"]FR[/TD]
[TD="class: xl70"]No[/TD]
[TD="class: xl66"]Day[/TD]
[TD="class: xl70"]N/A[/TD]
[TD="class: xl66"]2015[/TD]
[/TR]
[TR]
[TD="class: xl69, align: right"]3[/TD]
[TD="class: xl70"]Yes[/TD]
[TD="class: xl71"]48550[/TD]
[TD="class: xl72"]Gregory[/TD]
[TD="class: xl72"]Craig [/TD]
[TD="class: xl70"]EN[/TD]
[TD="class: xl70"]Yes[/TD]
[TD="class: xl66"]Day[/TD]
[TD="class: xl70"]N/A[/TD]
[TD="class: xl66"]2015[/TD]
[/TR]
[TR]
[TD="class: xl69, align: right"]4[/TD]
[TD="class: xl70"]Yes[/TD]
[TD="class: xl71"]66810[/TD]
[TD="class: xl72"]Rolance[/TD]
[TD="class: xl72"]Catherine [/TD]
[TD="class: xl70"]EN[/TD]
[TD="class: xl70"]Yes[/TD]
[TD="class: xl66"]Day[/TD]
[TD="class: xl70"]N/A[/TD]
[TD="class: xl66"]2016[/TD]
[/TR]
[TR]
[TD="class: xl69, align: right"]5[/TD]
[TD="class: xl70"]No[/TD]
[TD="class: xl71"]68910[/TD]
[TD="class: xl72"]Marie[/TD]
[TD="class: xl72"]Pierre[/TD]
[TD="class: xl70"]FR[/TD]
[TD="class: xl70"]No[/TD]
[TD="class: xl66"]Day[/TD]
[TD="class: xl70"]N/A[/TD]
[TD="class: xl66"]2015[/TD]
[/TR]
[TR]
[TD="class: xl69, align: right"]6[/TD]
[TD="class: xl70"]Yes[/TD]
[TD="class: xl71"]71030[/TD]
[TD="class: xl72"]Franco[/TD]
[TD="class: xl72"]Brian [/TD]
[TD="class: xl70"]EF[/TD]
[TD="class: xl70"]Yes[/TD]
[TD="class: xl66"]Day[/TD]
[TD="class: xl70"]N/A[/TD]
[TD="class: xl66"]2015[/TD]
[/TR]
</tbody>[/TABLE]


Hi guys,

i would need some help with this one.. seems simple enough, but for the life of me, i cant get it... please help!!
i need to calculate the total unique count (frequency) of EMP (column C), who were physically present (Yes in column J) for each of the years (2015 or 2016 in column G)

2015: i need a formula

2016: i need a formula





please please please help!!

 

[Aladin Akyurek]

control+shift+enter, not just enter...

=sum(if(frequency(if($g$2:$g$6="yes",if($j$2:$j$6=2015,$c$2:$c$6)),$c$2:$c$6),1))

 

[belkacem]

hi
try this :

 

[FDibbins]

hi
try this :

Looks like you forgot to add the formula?

 

[belkacem]

=SUMPRODUCT(($B$2:$B$7="Yes")*($J$2:$J$7=2015);$C$2:$C$7)

=SUMPRODUCT(($B$2:$B$7="Yes")*($J$2:$J$7=2016);$C$2:$C$7)

 

[Boubiche]

control+shift+enter, not just enter...

=sum(if(frequency(if($g$2:$g$6="yes",if($j$2:$j$6=2015,$c$2:$c$6)),$c$2:$c$6),1))

Oh my.. thank you so much...

will this work with several hundred rows of data?? (if i change formula .. of course..)


thanks!!

 

[Aladin Akyurek]

Oh my.. thank you so much...

You are welcome.

will this work with several hundred rows of data?? (if i change formula .. of course..)

thanks!!

Yes, it would.