Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JRD JRD is offline
external usenet poster
 
Posts: 60
Default counting function but not double counting duplicates

Can anyone help
A B C
1 John 01/10/07 Yes
2 John 01/10/07 Yes
3 John 02/10/07 Yes
4 Andrew 01/10/07 Yes
5 Andrew 01/10/07 Yes
6 Andrew 02/10/07 Yes

I need to count the number of Yes in column C but if columns A and B are the
same in more than 1 row then I do not want to count the yes.

Therefore in the above example the number of yes that I want excel to count
would be 4 (as row 1 and 2 are duplicate as are row 4 and 5, therefore the
yes in column C in these rows are not counted)

Thanks

John

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 256
Default counting function but not double counting duplicates

Try...

=SUMPRODUCT(--(MATCH(A2:A7&B2:B7,A2:A7&B2:B7,0)=ROW(A2:A7)-ROW(A2)+1),--(
C2:C7="Yes"))

Hope this helps!

In article ,
JRD wrote:

Can anyone help
A B C
1 John 01/10/07 Yes
2 John 01/10/07 Yes
3 John 02/10/07 Yes
4 Andrew 01/10/07 Yes
5 Andrew 01/10/07 Yes
6 Andrew 02/10/07 Yes

I need to count the number of Yes in column C but if columns A and B are the
same in more than 1 row then I do not want to count the yes.

Therefore in the above example the number of yes that I want excel to count
would be 4 (as row 1 and 2 are duplicate as are row 4 and 5, therefore the
yes in column C in these rows are not counted)

Thanks

John

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default counting function but not double counting duplicates

=SUM(N(FREQUENCY(IF(rngC="Yes",MATCH(rngA&rngB,rng A&rngB,0)),MATCH(rngA&rngB,rngA&rngB,0))0))

ctrl+shift+enter, not just enter


"JRD" wrote:

Can anyone help
A B C
1 John 01/10/07 Yes
2 John 01/10/07 Yes
3 John 02/10/07 Yes
4 Andrew 01/10/07 Yes
5 Andrew 01/10/07 Yes
6 Andrew 02/10/07 Yes

I need to count the number of Yes in column C but if columns A and B are the
same in more than 1 row then I do not want to count the yes.

Therefore in the above example the number of yes that I want excel to count
would be 4 (as row 1 and 2 are duplicate as are row 4 and 5, therefore the
yes in column C in these rows are not counted)

Thanks

John

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
Counting Names in a Column, Ignoring Duplicates Bruce Excel Discussion (Misc queries) 8 March 18th 07 02:01 PM
Counting duplicates Neil Excel Discussion (Misc queries) 11 November 15th 06 12:35 AM
Counting and duplicates Jeff Excel Discussion (Misc queries) 1 October 23rd 06 03:18 PM
counting duplicates Among Many Sheets, Possible?? Mhz New Users to Excel 5 July 5th 06 02:23 AM
Double Counting in Pivot Tables CYB Excel Discussion (Misc queries) 0 August 11th 05 12:18 AM


All times are GMT +1. The time now is 06:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"