I know this is an AppSheet community...but th...

AppSheet AppSheet Q&A
1

I know this is an AppSheet community…but there are some pretty knowledgeable folks here when it comes to formulation.

I’ve got a situation that I need help with.

This formula has work well to count the unique customer numbers amongst a huge amount of data:

=arrayformula(countunique(filter(‘Schedule and Call Report’!$K$2:$K,‘Schedule and Call Report’!$BC$2:$BC=“xxxxxx@xxxxxxxx.com”,‘Schedule and Call Report’!$AH$2:$AH=“Yes”))))

What I need to do now since it is a new year is dissect the data by calendar years, 2017, 2018, etc.

I thought that this would be the formula to do this:

=arrayformula(countunique(filter(‘Schedule and Call Report’!$K$2:$K,‘Schedule and Call Report’!$BC$2:$BC=“xxxxxx@xxxxxxxx.com”,‘Schedule and Call Report’!$AH$2:$AH=“Yes”,‘Schedule and Call Report’!c:c,“>=1/1/2018”,‘Schedule and Call Report’!c:c,“<=12/31/2018”))))

However, all this does is give me a zero when is should be 7 as my rep have entered 7 call reports, all for different (unique) customers.

Is there anyone out there that can help me with the format and where information should be entered in the formula to generate the accurate result?

In addition, this must populate automatically as new records are inputted which the original formula (first listed above) has done.

Thank you!

2

#ERROR!

3

@Bellave_Jayaram, no go…I get #error!

4

@Aleksi_Alkio, anything come time mind for you on how to make this work?

5

Try this:

=arrayformula(countunique(filter(‘Schedule and Call Report’!$K$2:$K,‘Schedule and Call Report’!$BC$2:$BC=“xxxxxx@xxxxxxxx.com”,‘Schedule and Call Report’!$AH$2:$AH=“Yes”,‘Schedule and Call Report’!C2:C>=DATE(2018,1,1),‘Schedule and Call Report’!C2:C<=DATE(2018,12,31)))))

6

@Bellave_Jayaram HOLY S@%&!

It works.

That effort is worth a full day’s wage, thank you very much.

I actually thought about formatting the dates like that, but I only assumed it wouldn’t make a difference.

I would love to know why that format works and the other one (1/1/2018 does not…but works in my other formulas).

Thank you very much.