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

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

jme_4477

New Member
Joined
Mar 24, 2016
Messages
43
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

Well-known Member
Joined
Dec 30, 2008
Messages
7,984
Office Version
2019
Platform
Windows
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))
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,984
Office Version
2019
Platform
Windows
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

New Member
Joined
Mar 24, 2016
Messages
43
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:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,984
Office Version
2019
Platform
Windows
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

New Member
Joined
Mar 24, 2016
Messages
43
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

Well-known Member
Joined
Dec 30, 2008
Messages
7,984
Office Version
2019
Platform
Windows
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:

jme_4477

New Member
Joined
Mar 24, 2016
Messages
43
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 :)
 

Forum statistics

Threads
1,089,376
Messages
5,407,903
Members
403,169
Latest member
Luna17

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top