Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountIF(A9:A20, B9 not equal B10, B10 not equal B11, etc.)
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountIF(A9:A20, B9 not equal B10, B10 not equal B11, etc.)
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 "Red Herald" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountIF(A9:A20, B9 not equal B10, B10 not equal B11, etc.)
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 "Red Herald" <Red_Herald_at_donotspam_my_hotmail_dot_com wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can I make cell "yes" equal 1, "no" equal 0 | Excel Discussion (Misc queries) | |||
Countif does not equal | Excel Worksheet Functions | |||
Countif a corresponding value is not equal to zero or blank | Excel Worksheet Functions | |||
COUNTIF based on several criteria incl. a "does not equal" criteri | Excel Worksheet Functions | |||
CountIf not Equal to. | Excel Discussion (Misc queries) |