Frequency IF

[ophiuchus]

Hi all

I've been coming here for information for ages and just took the plunge on the Dashboard course and thought I'd get involved in the community. I've also got a question about the frequency formula because I can't seem to get it to work as I want.

I have a cell whereby I am entering in a date in to cell B2 - the date is typically a Monday as it's the start of the week. Then I am trying to count the number of unique clients I have in column B (ClientID) in data sheet when the date in column A (Date) is greater than and equal to the date in B2.

{=FREQUENCY(IF(Date>=B2,ClientID),10000000)}

The value of 10000000 is there because the ClientID is numeric and this number is higher than the largest ClientID.

Thanks in advance for any assistance.

Mark

 

[Luke M]

I think this array formula is what you are looking for:
=SUM(IF(FREQUENCY(IF(Date<>"",
IF(Date>=B2,MATCH("~"&ClientID&"",ClientID&"",))),
ROW(Date)-MIN(ROW(Date))+1),1))

Remember to confirm with Ctrl+Shift+Enter, not just Enter.

 

[ophiuchus]

Thanks for the quick response.

The formula works on a count of all ClientIDs after a date but not the unique ones within the date range.

What exactly will the match do with the ~? I've never used it in a formula before.

 

[Haseeb A]

Hello Mark,

Your original formula should work, with some addition.

=SUM(IF(FREQUENCY(IF(Date>=B2,ClientID),ClientID),1))

 

[Luke M]

Hmm, it seems to work in my workbook (see attached). Data is on Sheet1, criteria and formula on Sheet2.
To answer your question, the ~ acts as a wildcard in the MATCH, so it doesn't really do much in this case. I admit I copied the formula from another thread.

 

[Luke M]

Hello Mark,

Your original formula should work, with some addition.

=SUM(IF(FREQUENCY(IF(Date>=B2,ClientID),ClientID),1))

FREQUENCY doesn't seem to like being fed strings, it wants numbers.
I suppose the ClientID's could be numbers...Perhaps OP can clarify?

 

[SirJB7]

@Luke M
Hi!
I think that OP wrote it were numbers:

The value of 10000000 is there because the ClientID is numeric and this number is higher than the largest ClientID.

Regards!

 

[ophiuchus]

Thanks for the help everyone.

Both of these worked for me in the end.
=SUM(IF(FREQUENCY(IF(Date>=B2,ClientID),ClientID),1))

=SUM(IF(FREQUENCY(IF(Date<>"",IF(Date>=B2,MATCH(ClientID,ClientID,))),ROW(Date)-MIN(ROW(Date))+1),1))