Count if ? formula needed!

[katagrga]

I'm am still searching for an answer to this. I am needing to count the
number of customer's with the occurance. The formula below counts the number
of occuraces per customer. Any help would be much appreciated!





Thanks so much for your response! I am almost there, but I need a bit more
help. I only want to count a name one time. From the example below, I need
to get the answer of 4. (Dan Smith has several cks written on duplicate
dates, but I only want to count as 1 not 2.) Is that possible?

One way to do this is to extract unique rows from your range to C1 [you need
to have headers in the first row]
(Data->Filter->Advance Filter... Copy To, Unique Values only)

Once you get the unique values in Col C&D enter this in Col E
=SUMPRODUCT(--($A$2:$A$17=C2),--($B$2:$B$17=D2))
change 17 to your last row of data.

Now you can count cells which are more than 1 in Col E to get what you want.
btw I got 5 from the sample data.
=COUNTIF(E2:E11,">1")

See http://wikisend.com/download/602840/Count Unique.xls for the file I used
to test the above.

Anyone out there have any ideas how I can get this information? I need a
count of the number of customers who wrote bad checks that wrote them on the
same date. So of the customers with bad checks how many had more than one
check written on the same date. Please help!

Was this post helpful to you?

Why should I rate a post?





Manage Your Profile |Contact us
© 2009 Microsoft Corporation. All rights reserved. Contact Us |Terms of Use
|Trademarks |Privacy Statement

 

[megangomez]

You can use a pivot table to summarize the data. Using your example: In
Excel 2007, Select the data, Select "Pivot Table" on the Insert tab, then add
fields from the Pivot Table field list as follows:

Row Lables = A, B
Sum Values = Count of B

You should get a table that shows each person's name, the grand total of
occurances per person, then a total number of occurances per date per person.

As you add occurances to the data list, you will need to update the pivot
table by clicking the "refresh" button on the Pivot Table Options tab.

Hope that helps!

 

[katagrga]

Thanks so much for your reply. I have not worked with pivot tables before.
I have all of the information in the tables as you instructed, is there a way
to count the number of customers who have 2 or more of the same date? I have
over 5000 entries, so I can not count them manually. Please excuse my lack
of experience. I really appreciate your help!

 

[megangomez]

Okay, to count how many people are multiple-bad-check-per-day-writers, no
matter how many times they did it, try this:

Re-arrange your pivot table fields this way:

Column Labels = B
Row Labels = A
Values = Count of B

Your pivot table should now show all the names alphebetically in the first
column, then a column for each individual date. The dated columns will show
the total number of bad checks written by each person on that date (if any).

NEXT: In the first blank cell after the first person's row in your pivot
table, write the following formula:


=MAX(rowstart:rowend)

Replace "rowstart:rowend" with the cell reference for the first dated column
through the last dated column in that person's row. (Don't include the Grand
total column!!) This formula should return the largest daily "bad check"
total for that person. Copy the formula down so each row now has a "max"
value.

At the bottom of the table, enter this formula as a subtotal to the MAX
formulas:

COUNTIF(columnstart:columnend,">1")

Replace "columnstart:columnend" with the cell reference for the first row
through the last row. (Don't include the Grand total row!!) This formula
will count how many people had a maximum per-day-bad-check-count greater than
1.