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

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

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