I have a dynamic spreadsheet currently with 10,000+ rows and 23 columns, about 5,000 rows will add monthly. It is a commission report for 17 sales reps.
I want to track how many different customers a rep did business with during a particular month.
I am currently using a pivot table, but that requires me to manually input the values each month.
I would prefer to put a formula into the destination cell, as the rest of my report is automated using VBA. ( I am new to VBA)
I need to count the number of "unique records" (text) in column H, matching the criteria in 3 other columns (all text).
Column 'B' matches the name of the rep,(John Smith), column 'J' the revenue type (rental), column 'W' the month (JAN) (all text, no blanks in any of the 4 columns).
This is what I tried, but only get a value of 1, my pivot table shows 109
( I get the same answer with an array formula or not)
I also tried using COUNTIFS combined with CountUniqueValues (tied to a VBA function).
CountUniqueValues works, but I couldn't figure out how to tie the COUNTIFS to it, so I was getting the total Unique Values at 1,129 (total customer base).
Thank you in advance, I really appreciate any help
Wayne M
I would like to know how many different customer (H), Ray A.(B) did rental (J) business with in Feb (W). Answer should be 3
Sample Data.xlsm
Bookmarks