COUNTIFS, EOMONTH and TODAY :)

Hey,

I would like to count all employed people(Employed) who graduated from uni (G) and received professional qualification (Q) during the month of May. So far this formula works well;

=COUNTIFS('Sheet 2'!B:B,"Employed",'Sheet 2'!R:R,"G",'Sheet 2'!AB:AB,"Q",'Sheet 2'!Z:Z,">4/30/2015")

However in order to avoid manually updating the "date" criteria every month when I update this sheet, I'd like to automate it. Therefore I want a formula/macro button that automatically counts the number employed gardautes who qualified over the past month (as of the end of the month).

To do that, EOMONTH sounded perfect. Therefore I wrote this formula and thought it would work;

=COUNTIFS('Sheet 2'!B:B,"Employed",'Sheet 2'!R:R,"G",'Sheet 2'!AB:AB,"Q",'Sheet 2'!Z:Z,">EOMONTH(TODAY(),-1)")

However the function with eomonth part gives me the same wrong result no matter what changes I bring to the EOMONTH "month" part. regardless if it's -1, -10/-20 it gives me the same wrong answer. I don't know what went wrong ? My dates are all properly formatted.

Also, a macro button that could help me update the month part of my function would be welcome too

Any other ideas on how I could update that to automatically get the result I want is are welcome


Please please help me thank you
Thank you!

Try

=SUMPRODUCT(--(Sheet2!B:B="Employed"),--(Sheet2!R:R="G"),--(Sheet2!AB:AB="Q"),--(MONTH(Sheet2!Z:Z)=MONTH(TODAY())-1))

or this ....

=COUNTIFS(Sheet2!B:B,"Employed",Sheet2!R:R,"G",Sheet2!AB:AB,"Q",Sheet2!Z:Z,">" & B1)

where B1=EOMONTH(TODAY(),-1)

COUNTIFS does not like the volatile expression placed in B1

Well, well!! I tried using COUNTIFS with EOMONTH and constantly failed with formula errors (??!!!). I have now retried it and it works! Not sure what was wrong in my earlier attempts so my previous comment was wrong.

This worked OK.

=COUNTIFS(Sheet2!B:B,"Employed",Sheet2!R:R,"G",Sheet2!AB:AB,"Q",Sheet2!Z:Z,">" & EOMONTH(TODAY(),-1))

This worked OK.

=COUNTIFS(Sheet2!B:B,"Employed",Sheet2!R:R,"G",Sheet2!AB:AB,"Q",Sheet2!Z:Z,">" & EOMONTH(TODAY(),-1))

Thank you so much JohnTopley! You just saved my job, your formula works perfectly fine! Thats really exactly what I needed.

thanks a lot

Thanks for the ideas everyone