Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count if ? formula needed!
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? "Sheeloo" wrote: 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. "katagrga" wrote: 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! "katagrga" wrote: I am trying to find the number subtotals that have a duplicate date within them. I have the info subtotaled by name. The answer I need from the example below is 4. I appreciate your suggestions. A B Alex Jones 1/2/08 Alex Jones 1/2/08 Alex Jones 3/4/08 4 Ben Jackson 5/1/09 Ben Jackson 5/12/09 2 Craig Meyer 7/2/08 Craig Meyer 7/2/08 2 Dan Smith 3/15/09 Dan Smith 3/24/09 Dan Smith 3/24/09 Dan Smith 5/7/09 Dan Smith 5/7/09 Dan Smith 5/7/09 6 Eric Roberts 4/6/09 Eric Roberts 4/6/09 Eric Roberts 5/17/09 3 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count if ? formula needed!
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count if ? formula needed!
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" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count if ? formula needed!
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help needed with excel, need to count the number ofpeople here each hour. | Excel Worksheet Functions | |||
formula for current stock count needed | Excel Worksheet Functions | |||
2 conditions needed to check and count of items based on that | Excel Worksheet Functions | |||
FORMULA NEEDED TO COUNT DAYS IN EXCEL BETWEEN DATES | Excel Worksheet Functions | |||
More Help Needed with Count formula | Excel Worksheet Functions |