Page 1 of 2 12 LastLast
Results 1 to 10 of 18

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

  1. #1
    New Member
    Join Date
    Mar 2016
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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/17eJ...ew?usp=sharing

  2. #2
    New Member
    Join Date
    Mar 2016
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  3. #3
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,742
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

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

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

  4. #4
    New Member
    Join Date
    Mar 2016
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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...#gid=795144365

    Thank you again

  5. #5
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,742
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  6. #6
    New Member
    Join Date
    Mar 2016
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by jme_4477; Oct 7th, 2019 at 02:43 PM.

  7. #7
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,742
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

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

    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

  8. #8
    New Member
    Join Date
    Mar 2016
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  9. #9
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,742
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

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

    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).
    Last edited by jasonb75; Oct 13th, 2019 at 05:11 AM.

  10. #10
    New Member
    Join Date
    Mar 2016
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •