CountIf or SumProduct Formula Problem

**reb2u** · 11-06-2013, 04:58 PM

I'm simplifying this a little, but essentially I have a spreadsheet with application dates in column A, office codes in column B, customer PINs in column C, approval dates in column D, denial dates in column E, and # of prev apps in column F.

Customers may apply for a process and be approved the first time, but if not, they may continue to apply as many times as they wish, so I can have multiple rows of data for the same customer.

The # of apps cell in a corresponding record is left blank for the first app, but each subsequent app, if any, are numbered: 2, 3, 4, etc.

I've been able to track the total number of applications coming out of each office, but I'm seeing a trend that some offices have much higher rates of first time approvals with their customers than others and believe these offices are doing a better job of assisting their customers in meeting the requirements. I want to be able to count the number of customers each office is seeing and what percentage of those were approved the first time they applied.

I've been trying different CountIf and SumProduct formulas, but just haven't been able to figure this out. My brain is fried! Can you help? THANKS!

**TMS** · 11-06-2013, 06:03 PM

How would you have us help you? While a description of your data obviously means something to you, there's not much that we can do with it.

I suggest you upload a sample workbook with some typical, but anonymized, data.

Regards, TMS

**daddylonglegs** · 11-06-2013, 06:27 PM

Originally Posted by reb2u

...I want to be able to count the number of customers each office is seeing and what percentage of those were approved the first time they applied.......Can you help?....

To get the number of different customers for a specific office you should be able to count blanks in column F (first application) for that office so you can do that with SUMPRODUCT something like

=SUMPRODUCT((B$2:B$1000=H2)*(F$2:F$1000=""))

where H2 is a specific office code

For the percentage of those approved first time, I'm assuming you can count those rows where column D has a number (a date), i.e. either

=SUMPRODUCT((B$2:B$1000=H2)*(F$2:F$1000="")*ISNUMBER(D$2:D$1000))/SUMPRODUCT((B$2:B$1000=H2)*(F$2:F$1000=""))

or a shorter array formula

=AVERAGE(IF((B$2:B$1000=H2)*(F$2:F$1000=""),IF(ISNUMBER(D$2:D$1000),1,0)))

confirmed with CTRL+SHIFT+ENTER