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!
#ERROR!
@Bellave_Jayaram, no goโฆI get #error!
@Aleksi_Alkio, anything come time mind for you on how to make this work?
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)))))
@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.
User | Count |
---|---|
40 | |
35 | |
30 | |
23 | |
17 |