Complex Formula to Count # of calls made to Customers.

[bensko]

I have a list of 973 projects for 451 unique customers with a combined 541 locations. I have a column that relationship managers use to track phone calls. With each call, they populate the cell in increments of 1. For each of the 541 locations, I would like to count which ones have been phoned. Since 1 location could have 3 projects(rows) with several calls made, it should only count them once.

L8:L981 is the column that will accumulate calls in increments of 1
AD8:AD981 is the column of 541 customer locations.

I have tried a variety of sum, if, and, frequency, & match in several different variations, but cannot seem to get it to count correctly.

Appreciate any help in advance

Ben

 

[barry houdini]

Hello Ben, try this formula

=SUM(IF(FREQUENCY(IF(L8:L981>0,IF(AD8:AD891<>"",MATCH(AD8:AD981,AD8:AD981,0))),ROW(L8:L891)-ROW(L8)+1),1))

confirmed with CTRL+SHIFT+ENTER

 

[bensko]

Thank you Barry, that worked perfectly - I should have tried the message board sooner.... Oh well, thats how we learn right.

Ben