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

[jme_4477]

I've been trying to create a formula with multiple criteria, and I can't do it. If there is any number in column C, then I need to look at the dates in two columns (J and AB). If either J or AB has a date more than 28 days ago (based on today), I need it to count how many students have no call for greater than 28 days. Does that make sense?


https://drive.google.com/file/d/17eJvCj3GIUgHOq_0sHGpyUKXXHuP2JWw/view?usp=sharing

 

[jme_4477]

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.

 

[jasonb75]

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))

 

[jme_4477]

Thank you SO much for your help!! That formula worked perfectly! I have to use it in a few different spreadsheets though, and when I put it into one that is very similar (even with the same column titles), it doesn't work correctly. Can you take a look at Sheet 1 of the spreadsheet?

https://docs.google.com/spreadsheets/d/17eJvCj3GIUgHOq_0sHGpyUKXXHuP2JWw/edit#gid=795144365

Thank you again

 

[jasonb75]

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.

 

[jme_4477]

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.)

 

[jasonb75]

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

 

[jme_4477]

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?

 

[jasonb75]

I think that this should solve it now, I had missed out an important part of the formula

=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).

 

[jme_4477]

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