Problem with Unique Sum/Count with Criteria

[tdemana]

Using Excel 2010, I have a sheet that I'm using as a base for a sheet of metrics.



Sheet1 has my data, with named ranges, and Sheet2 has my metrics on it.

I have multiple client names (Named range = Client_Name)that could be associated with a row of data, and I need to know how many unique entries occur here after filtering for another criteria (where named range Client_ClientNonClientRelated = On Site Client Related Time).

I'm not sure how to go about this. This is what I've tried, but I get a 0 as a result.

=SUM(IFS(Client_ClientNonClientRelated,"=On Site Client Related Time",FREQUENCY(MATCH(ClientName,ClientName,0),MATCH(ClientName,ClientName,0))>0,1))

Any suggestions?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<!---->
<!---->
<!---->

 

[Domenic]

Try...

=SUM(IF(FREQUENCY(IF(Client_ClientNonClientRelated="On Site Client Related Time",IF(ClientName<>"",MATCH(ClientName,ClientName,0))),ROW(ClientName)-MIN(ROW(ClientName))+1)>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

 

[tdemana]

I got the full formula for what I was into by playing with what you started, and ended up with this:

=COUNT(1/FREQUENCY(IF(Client_ClientNonClientRelated="On Site Client Related Time",IF(ClientName<>"",MATCH(ClientName,ClientName,0))),ROW(ClientName)-ROW(OFFSET(ClientName,,,1,1))+1))


Thanks!

 

[Domenic]

You're welcome! Note, however, that OFFSET is a volatile function. Volatile functions recalculate each time a change of data occurs in any cell on any worksheet, regardless of whether a cell they're referencing has changed. As a result , recalculation times are prolonged. Therefore, you may want to replace...

ROW(OFFSET(ClientName,,,1,1))+1

with

MIN(ROW(ClientName))+1

 

[tdemana]

Thanks for the information. You were quite right. This made the processing MUCH quicker.

 

[Domenic]

You're welcome! Thanks for the feedback!