Hi, I'm stuck and would very much appreciate if someone could help me think through my SUMPRODUCT ( ? )issue.
company col1 col2 col3 col4
apple entry compliant 31-Jan-13 01-Jan-12 entry compliant
apple 31-Jan-13 31-Jan-13 entry compliant entry compliant
apple entry compliant 31-Jan-13 entry compliant entry compliant
apple 01-May-13 31-Jan-13 entry compliant entry compliant
apple entry compliant 31-Jan-13 entry compliant entry compliant
barnes and noble entry compliant entry compliant entry compliant 01-Jan-12
barnes and noble 06-Jul-13 entry compliant entry compliant 01-Jan-12
barnes and noble entry compliant entry compliant entry compliant 01-Jan-12
cisco entry compliant entry compliant entry compliant entry compliant
cisco entry compliant entry compliant entry compliant entry compliant
For each company, I want there to be at least one instance of a date field in each col (col1, col2, col3, col4). So count=1 if there is at least one date in a column (more than 1 date, will still count as 1), grouped by company. The first company "apple" should have a count total of 3 since the 4th column does not have any dates at all.
the kicker: the number of companies and columns will increase with every report (don't know by how many), so I was planning to set # of rows to 999, and account for all columns (A:IV).
I've tried so many different iterations of this but I can't get it to work --> =SUMPRODUCT(($A$2:$A$999=$A2)*($B$2:$IV$999>"0"))
Would I use SUMPRODUCT to do this?
Help!
Bookmarks