Countifs formula based on multiple criteria and a date less than today

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
To clarify....It's not if either column has no date within 28 days. It's if NEITHER column has a date within 28 days. So if one column has a date over 28 days ago, but the other column has one less than 28 days ago, then I don't want that counted in the total. I hope this makes sense.
 
Upvote 0
With no expected outcome it is unclear how cells with no date should be handled. Assuming that an empty cell should be considered as no call within 28 days, try this one

=COUNTIFS(Table_1[User ID],">0",Table_1[WC Date],CHOOSE({1,2},"=","<"&TODAY()-28),Table_1[Oct Call],CHOOSE({1,2},"=","<"&TODAY()-28))
 
Upvote 0
There is no 'Table1' in sheet1, when I enter the formula into sheet1 it is counting the dates in sheet2 (where 'Table1' exists).

If you're using the formula in google docs, or anything other than excel then it might behave differently which would go some way to explaining the #ERROR result.
 
Upvote 0
Thank you again so much for your help. I really, really appreciate it!! I am using this in Excel (not Google Sheets), but I can't attach my spreadsheet here so that's why I included Google Sheets. How can I create a Table in excel for Sheet 1? (Keep in mind that when I'm using these formulas, they are not in different sheets, but they are in different Excel files altogether.)
 
Last edited:
Upvote 0
If you created the sample in excel then copied it over to google sheets then there must be a table in the source file. If there was no table then excel would not show an error because it would not let you enter the formula, instead you would see a pop up message saying that 'There is a problem with this formula'. I thought that the #ERROR result was google sheets way of dealing with the same problem, but if you have used excel then this would not be the case.

Because you've used google sheets, the formula is behaving differently to how it would in excel. It would be better if you could attach the excel file with the error using google drive instead of google docs, that way it will show the correct excel error instead of a google equivalent.

Although it appears that you might not need it now, for a good 'how to' guide on tables see here, https://www.contextures.com/xlExcelTable01.html#Video
 
Upvote 0
Thank you again so much for your help!! Also, that link on tables was very helpful, and I think I got that figured out now :)

I do have one more problem though. I don't think the formula is calculating correctly. In the file (below it's in Excel version, which is what I use), you can see the formula in Sheet 2 AI1. It only shows 2 students having no call (either in WC Date or OCT Call) for more than 28 days. I think there should be 6 students who have no call for more than 28 days though:
Appl, Donner
Ather, Steeven,
Bart, Karl,
Bausner, Sarinta
Baseter, Leiftner
Bodate, Jack

Basically, if date or dates in column K or N are more than 28 days ago or blank (meaning no call), then it should count in the total. And if there's one date in either column, though, within 28 days in columns K or N (even if the other column is blank or if it has an older date in it), then it should not be counting.

https://1drv.ms/x/s!Aigq1WyI8ss7go824OsQGf_G2Z16CQ

Does this make sense?
 
Upvote 0
I think that this should solve it now, I had missed out an important part of the formula :eek:

=SUMPRODUCT(COUNTIFS(Table2[User ID],">0",Table2[WC Date],CHOOSE({1;2},"=","<"&TODAY()-28),Table2[OCT Call],CHOOSE({1,2},"=","<"&TODAY()-28)))

The way the formula is written it counts in 4 parts

Blank WC date + Blank Oct Call
Blank WC date + Oct Call over 28 days
WC date over 28 days + Blank Oct Call
WC date over 28 days + Oct Call over 28 days

Then adds them together. The incorrect formula, without sumproduct, was only giving you the result from the first part (blank + blank) because there was nothing to add the results together.

edit:- to clarify, the formula will need to be changed in the sheets where you were getting the correct result from the original formula, otherwise any changes to the data may not be counted correctly.

Also, I nearly forgot to mention that I had to change a comma to a semi-colon to make it work correctly, so this will also need changing in the sheets where the formula appears to work. (highlighted in the formula above).
 
Last edited:
Upvote 0
You are truly AMAZING!!! I cannot thank you enough for your help with this!!!!!!!!!!!!!!!! There really are no words to express how thankful I am for you :)
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top