COUNTIFS, EOMONTH and TODAY :)

[natnato]

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!

 

[Cosmic Wizard]

Hi there,

There is a thread which had some great formula which may help you, as they combine dates and products and numbers!

It called "COUNTIFS or SUMPRODUCT" started by dunmore83.

One of the formulas are =SUMPRODUCT(--(MONTH($E$7:$E$46)=$A3),--(YEAR($E$7:$E$46)=$A2),--($N$7:$N$46=$A4),$T$7:$T$46).

Check it out, if it not what you need, re-post and we will try and sort it!

I also used them with drop down boxes which meant that I could change the month, year, and in your case the uni and it would still work!

 

[RonB1111]

Try: (moved the last " and added & as shown in red below):

=COUNTIFS('Sheet 2'!B:B,"Employed",'Sheet 2'!R:R,"G",'Sheet 2'!AB:AB,"Q",'Sheet 2'!Z:Z,">[COLOR=#ff0000]"&[/COLOR]EOMONTH(TODAY(),-1))

 

[natnato]

RONB1111 That worked well! Thanks for the ideas everyone

 

[RonB1111]

RONB1111 That worked well! Thanks for the ideas everyone

You're welcome!