count the unique values with conditions

chibimomo

New Member
Joined
Jul 25, 2013
Messages
5
Hi,

i'm having trouble trying to count the unique values/entries on column B (name) that has the ff conditions the date should be from july 1-5 for week one, week two ..etc, and team (red, etc..) as seen below.
if i need the count of unique names for the first week on red team it should return = 3. i tried using sumproduct(((date>=jul1)*(date<=jul5)),--(team="red").. but im stuck on how to only count the unique values.
please help. what formula should i use?

thank you.

1-Jul8-Jul
5-Jul12-Jul
TeamWeek OneWeek Two
Red3
Blue
datenameteamproduct
1-Julmikeredc
1-Juldavebluea
1-Julmikereda
1-Julpatreda
1-Julmikeredb
2-Julmikereda
2-Juldavebluea
2-Juldavebluea
2-Julcrisbluea
2-Julcrisblueb
3-Julmikeredb
4-Julbertredc
5-Julmikeredc

<tbody>
</tbody>
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Welcome to MrExcel.

Example:


Excel 2010
ABCD
101-Jul08-Jul
205-Jul12-Jul
3TeamWeek OneWeek Two
4Red30
5Blue20
6
7datenameteamproduct
801-Julmikeredc
901-Juldavebluea
1001-Julmikereda
1101-Julpatreda
1201-Julmikeredb
1302-Julmikereda
1402-Juldavebluea
1502-Juldavebluea
1602-Julcrisbluea
1702-Julcrisblueb
1803-Julmikeredb
1904-Julbertredc
2005-Julmikeredc
Sheet13
Cell Formulas
RangeFormula
B4{=SUM(IF(FREQUENCY(IF($A$8:$A$20>=B$1,IF($A$8:$A$20<=B$2,IF($C$8:$C$20=$A4,MATCH($B$8:$B$20,$B$8:$B$20,0)))),ROW($A$8:$A$20)-ROW($A$8)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Oh my! Thank you very much sir! :) thank you! i will study the formula you gave to better understand it.
again thank you very much. your a life saver.:biggrin:
 
Upvote 0
Hey Thanks for Writing!!

Another alternative if u like :)

=SUM(IFERROR(1/COUNTIF($B$8:$B$20,IF(IF(IF(($A$8:$A$20>=B$1)*($A$8:$A$20<=B$2)*($C$8:$C$20=$A4),MATCH($B$8:$B$20,$B$8:$B$20,0)),ROW($A$8:$A$20)-ROW($A$8)+1),$B$8:$B$20)),0))


Please make sure you press Control + Shift + Enter while entering the formula.

Thanks/Raj
 
Upvote 0
Hey Thanks for Writing!!

Another alternative if u like :)

=SUM(IFERROR(1/COUNTIF($B$8:$B$20,IF(IF(IF(($A$8:$A$20>=B$1)*($A$8:$A$20<=B$2)*($C$8:$C$20=$A4),MATCH($B$8:$B$20,$B$8:$B$20,0)),ROW($A$8:$A$20)-ROW($A$8)+1),$B$8:$B$20)),0))


Please make sure you press Control + Shift + Enter while entering the formula.

Thanks/Raj

Hi Raj, Im a bit confuse.. i was trying to understand the formula you gave, but i couldn't follow the 2nd "IF" whats the "ROW" function for? sorry for the trouble but can you explain the formula to me? thank you.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top