# COUNTIFS, EOMONTH and TODAY :)

#### natnato

##### New Member
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

Thank you!

Last edited:

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hi there,

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!

Last edited:
Try: (moved the last " and added & as shown in red below):
Code:
``=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))``

RONB1111 That worked well! Thanks for the ideas everyone

RONB1111 That worked well! Thanks for the ideas everyone

You're welcome!

Replies
0
Views
119
Replies
4
Views
207
Replies
10
Views
513
Replies
9
Views
159
Replies
17
Views
748

1,203,646
Messages
6,056,531
Members
444,872
Latest member
agutt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back