Forum Discussion
Using a Filtered Data Range
I have a datatable that is added to daily by date. I am using the formula
=COUNTIFS(FirstVisit,"Yes",Stake,A6)
to count number of "first time visits" to our facility for each group ("Stake").
However, I want to write the above formula to only count the data table for a specific month, like Feburary.
Any ideas how I might do this?
The data table has a time stamp field (VisitDate), First Time Visit, Stake (group they are from), contact name and phone (which are not really needed for a report.
Hi Stephen,
That could be something like this
=COUNTIFS(FirstVisit,"Yes",Stake,A6,VisitDate,">" & EOMONTH(DATE(2017,2,1),-1),VisitDate,"<=" & EOMONTH(DATE(2017,2,1),0))
where the DATE() you may generate as it is more convinient for you..
Perhaps bit easier it'll be to use
=SUMPRODUCT(--(FirstVisit="Yes"),--(Stake=A6),--(MONTH(VisitDate)=2))
2 Replies
Hi Stephen,
That could be something like this
=COUNTIFS(FirstVisit,"Yes",Stake,A6,VisitDate,">" & EOMONTH(DATE(2017,2,1),-1),VisitDate,"<=" & EOMONTH(DATE(2017,2,1),0))
where the DATE() you may generate as it is more convinient for you..
Perhaps bit easier it'll be to use
=SUMPRODUCT(--(FirstVisit="Yes"),--(Stake=A6),--(MONTH(VisitDate)=2))
Thanks Sergei!
I REALLY appreciate YOUR time answering this this question!! I spent HOURS googling for answers. Using both of the formulas below produced what I needed. I have never seen anything using the --(FirstVisit="Yes") type of functions. I did notice that they have to do with Arrays on the tooltips. I will have to study these more.
I am much more familiar with VBA functions and writing queries in Access, and haven't used Excel that much, but in the Library directorship that I am in, I don't have Access, only Excel.
Again, MANY thanks!
Steve Meyers
