Menu

count unique with mulitple criteria

Search this Thread

Prev

Previous Post

Next Post

Next

#17

Report Post

Posted to microsoft.public.excel.worksheet.functions

Don Guillett Don Guillett is offline

Don Guillett is offline

external usenet poster

Posts: 10,124

Default

count unique with mulitple criteria

I think it should
why?

--
Don Guillett
SalesAid Software

"PapaDos" wrote in message
...
Nice !

Isn't the last term of your "hardcoded" one wrong ?
A probable typo error, be ROW($A2:$A8) instead of
ROW($A2)...
--
Festina Lente

"Harlan Grove" wrote:

Domenic wrote...
....
If the data can contain the following...

Mike JOB1
Karen JOB2
Unfilled JOB1
Unfilled JOB2
Unfilled JOB1
Alex JOB2 <---
Alex JOB1 <---

...I believe the unique count should be 4. If this is correct, the
above formula will fail and return 3. Similar to the above formula,
mine can be shortened to...

=COUNT(1/FREQUENCY(IF(B$2:B$8=D2,IF(A$2:A$8="Unfilled",ROW( A$2:A$8)
-ROW(A$2)+1,MATCH(A$2:A$8,A$2:A$8,0))),ROW(A$2:A$8)-ROW(A$2)+1))
....

Or mine could be corrected to

=COUNT(1/(INDEX(TBL,0,2)=JOB)/IF(INDEX(TBL,0,1)="Unfilled",1,MATCH(INDEX(TBL,0,1 ),
IF(INDEX(TBL,0,2)=JOB,INDEX(TBL,0,1)),0)-1+MIN(ROW(TBL))=ROW(TBL)))

If I were to hardcode the ranges, it reduces to

=COUNT(1/($B2:$B8=D2)/IF($A2:$A8="Unfilled",1,MATCH($A2:$A8,
IF($B2:$B8=D2,$A2:$A8),0)-1+MIN(ROW($A2:$A8))=ROW($A2)))

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
"COUNTU" function in Excel to count unique entries in a range	WayneL	Excel Worksheet Functions	20	September 19th 08 03:50 AM
Total Summed Count of Numeric Repeat (Paired /Double Instance) Criteria	Sam via OfficeKB.com	Excel Worksheet Functions	2	March 20th 06 02:29 AM
how to count unique values in excel based on multiple criteria	IDBUGM	Excel Worksheet Functions	3	March 15th 06 04:00 PM
How do I count wildcard text meeting certain criteria in EXCEL?	cybermaksim	Excel Worksheet Functions	1	February 17th 06 03:03 AM
Count of unique items meeting condition	Tim C	Excel Worksheet Functions	1	November 12th 04 03:03 AM

All times are GMT +1. The time now is 11:11 PM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement