Using a Filtered Data Range

[Stephen Meyers]

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.

[#2 · Accepted solution]

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))

[#3]

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