counting unique values with two criteria

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
330
Hi there,

I have the following CSE formula wherein I am trying to find the number of unique values in column C (they pertain to employee ID # and there are several duplicates) if the value in column F is SF, and the value in column H matches what is in cell G33.

The formula is telling me i have too many arguments, but I am unclear as to what I am doing incorrectly. Thanks so much for any help!



=sum(if(frequency(if('labor rpt'!H3:H10277=G33,IF('labor rpt'!F3:F10277="SF",IF('labor rpt'!C3:C10277<>"",MATCH('labor rpt'!C3:C10277,'labor rpt'!C3:C10277,0)))),ROW('labor rpt'!C3:C10277)-MIN(ROW('labor rpt'!C3))+1,1))
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,300
You're missing a closing bracket after +1. Also, one normally uses MIN(ROW(...)) when referencing a range of cells, especially a named range. Otherwise, ROW('labor rpt'!C3:C10277)-ROW('labor rpt'!C3)+1 would suffice when referencing a single cell. Try...

Code:
=SUM(IF(FREQUENCY(IF('labor rpt'!H3:H10277=G33,IF('labor rpt'!F3:F10277="SF",IF('labor rpt'!C3:C10277<>"",MATCH('labor rpt'!C3:C10277,'labor rpt'!C3:C10277,0)))),ROW('labor rpt'!C3:C10277)-MIN(ROW('labor rpt'!C3:C10277))+1)>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,974
Messages
5,525,993
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top