Hi, I'm trying to use a countifs formula that does not count any data for 'Last Month'. See what I'm trying to do below. I would also like the formula that does not count 'This Month'.
Thanks in advance.
PS. I need it to be dynamic, cheers.
Attach an example of your workbook
You may also try:
This month
=COUNTIFS(A1:A100,"PW",B1:B100,">"&EOMONTH(TODAY(),-1))
This should ignore this month
=COUNTIFS(A1:A100,"PW",B1:B100,"<="&EOMONTH(TODAY(),-1))
Hi Roy, see attached
That's fine.
I've made it more dynamic by adding a drop down list to choose the area in H2. I've changed the formula to point to this cell so when you select a different area it will change the results.
The only problem you will have is if you add data then the formulas will need to be updated. You can solve this by making data into a Table. Read this
Here's an example using your data converted to a Table. Now if you add or remove data to the Table the formulas will adjust.
Excellent, thanks royUK. Works perfectly.
I do have a dumb question though. Because the formula is dynamic, if I enter the data in Aug but re-open the workbook in Sept, will the results change because the computer clock is now in Sept? Or will it only change if I change the data.
If it does change if I open the workbook in Sept, is there a way to lock the results in Aug?
Thanks again for your help, cheers.
The actual formulas are using TODAY() which is volatile so it will update as the date changes.
If you don't want that then you can enter a date in a cell to base the formulas on.
Thanks royUK, that's not good because I need the report to be static for the Aug figures and some managers may review the report later in Sept or Oct, so when they open it the figures will change.
Could you please use the attached file to show me what you mean by using a date to base the formula on, if it's not to much trouble, cheers.
Which version are you using? The one with the Table or the ordinary data?
I've amended the formula so the date is entered in K2, e.g. 1/8/21 for August, used the Table version
Ordinary, thanks
Here's the one without a Table, although using a Table is the best way.
Pleased to help.
Post back if you need further help.
Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.
Don’t have an account yet? Register yourself now and be a part of our community!
