Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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
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
can I make cell "yes" equal 1, "no" equal 0 can I make cell yes equal 1, no equa Excel Discussion (Misc queries) 4 April 22nd 23 06:09 AM
Countif does not equal JRD Excel Worksheet Functions 6 April 4th 23 10:51 AM
Countif a corresponding value is not equal to zero or blank raphiel2063 Excel Worksheet Functions 1 April 4th 08 03:18 PM
COUNTIF based on several criteria incl. a "does not equal" criteri MsBeverlee Excel Worksheet Functions 3 November 12th 07 09:32 PM
CountIf not Equal to. Pendelfin Excel Discussion (Misc queries) 4 February 27th 07 03:27 PM


All times are GMT +1. The time now is 07:34 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"