I have a sorted column of account numbers that I want to count.

I don't want to count more than one occurence of an account number if it has

the same work date as the preceding occurence. But differing account numbers

with the same work date should be counted.

Account # Work Date

====== =======

A6666666 09/10/08

A6666666 09/12/08

A5555555 09/12/08

A4444444 09/11/08

A4444444 09/11/08

A4444444 09/12/08

A4444444 09/13/08

In the above example I want to count both occurences of "66666666' because

they have different work dates. I want to count only three occurences of

"44444444", the first, third and fourth. My count total for the above

example should be "6".

How can I do this?

With your posted data in A1 thru B7, in C1 enter:

=A1 & B1 and copy down to see:

A6666666 9/10/2008 A666666639701

A6666666 9/12/2008 A666666639703

A5555555 9/12/2008 A555555539703

A4444444 9/11/2008 A444444439702

A4444444 9/11/2008 A444444439702

A4444444 9/12/2008 A444444439703

A4444444 9/13/2008 A444444439704

Then its just a matter of counting uniques in column C:

=SUM(1/COUNTIF(C1:C7,C1:C7)) displays 6

This is an array formula that must be entered with CNYTRL-SHFT-ENTER rather

than just the ENTER key.

--

Gary''s Student - gsnu200813

=A1 & B1 and copy down to see:

A6666666 9/10/2008 A666666639701

A6666666 9/12/2008 A666666639703

A5555555 9/12/2008 A555555539703

A4444444 9/11/2008 A444444439702

A4444444 9/11/2008 A444444439702

A4444444 9/12/2008 A444444439703

A4444444 9/13/2008 A444444439704

Then its just a matter of counting uniques in column C:

=SUM(1/COUNTIF(C1:C7,C1:C7)) displays 6

This is an array formula that must be entered with CNYTRL-SHFT-ENTER rather

than just the ENTER key.

--

Gary''s Student - gsnu200813

Hi,

You can also try this

HIghlight B2:B8 and Data > Filter > Advanced Filter. Now select Copy to

another location and select a blank cell in the copy to box (Cell A16).

Check the box for Unique records only. This procedure will extract the

unique dates to another location (say A16:A18). By the same procedure

outlined above, extract the unique dates to A10:A12. Now in cell B10, enter

the following formula

=SUMPRODUCT((COUNTIF($A$16:$A$18,$B$2:$B$8)=1)*($A$2:$A$8=$A10)) and copy

down.

--

Regards,

Ashish Mathur

Microsoft Excel MVP

www.ashishmathur.com

You can also try this

HIghlight B2:B8 and Data > Filter > Advanced Filter. Now select Copy to

another location and select a blank cell in the copy to box (Cell A16).

Check the box for Unique records only. This procedure will extract the

unique dates to another location (say A16:A18). By the same procedure

outlined above, extract the unique dates to A10:A12. Now in cell B10, enter

the following formula

=SUMPRODUCT((COUNTIF($A$16:$A$18,$B$2:$B$8)=1)*($A$2:$A$8=$A10)) and copy

down.

--

Regards,

Ashish Mathur

Microsoft Excel MVP

www.ashishmathur.com

1. EQUAL is not transitive or (EQUAL "" #*) ==> T

2. Why? 'Not equal' brings back whats 'equal'

3. Query from a Combo box for both equal to and not equal to.

4. Cities are Equal but Region Number is not equal

5. Adding every 5th row (e.g. B5 + B10 + B15 etc)

6. Equal variables not equal after all

8. PHP MySQL equal and not equal statement problem

9. COUNTIF based on several criteria incl. a "does not equal" cri

10. COUNTIF based on several criteria incl. a "does not equal" criteri

11. Only show rows where T1_F1 equals T2_F1 and T1_F2 equals T2_F2 and

12. Macro to Unhide MulipleRows if Cell B10 has a "X" otherwise hi

14. Only show rows where T1_F1 equals T2_F1 and T1_F2 equals T2_F2

15. Compare two file/colunms, hide row not does not equal list.

3 post • Page:**1** of **1**