Forum Discussion

Copper Contributor

Mar 03, 2017

Solved

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"). Ho...

Formulas & Functions

Need Help

SergeiBaklan

Mar 04, 2017

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

SergeiBaklan

Diamond Contributor

Mar 04, 2017

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

Stephen Meyers

Copper Contributor

Mar 05, 2017

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