Google-sheets – Checking Between Two Dates using SUMIFS

google sheets

I am using SUMIFS to sum values based on various criteria. So the following formula gives me a sum of all values in column D that are greater than zero.

=SUMIFS(D:D, J:J, "Bob", K:K, "Smith", D:D, ">0")

I now want to add in a criteria to check if a date value in Column A is within a certain date range. eg something like this:

=SUMIFS(D:D, J:J, "Bob", K:K, "Smith", D:D, ">0", A:A, AND(">05/04/2017", "<06/04/2018"))

That doesn't work though. Any ideas how to incorporate that date check?

Best Answer

It doesn't work because ">05/04/2017" isn't by itself a logical expression, it's only a criterion. Consider it an inconsistency inherited from excel.

Instead just respecify as needed.

=SUMIFS(D:D, 
J:J, "Bob", 
K:K, "Smith", 
D:D, ">0", 
A:A, ">05/04/2017", 
A:A, "<06/04/2018")

Related Solutions

Google Sheets – How to Make a Query That Checks for a Date

I couldn't find the trick for the real date cell to work yet, but you could have a work around if nothing else by adding the the single quote (') before the date in a format: YYYY-MM-DD in the Q4 cell.

That single quote means that the it will be parsed as string.

Google Sheets – Sending Email Notifications Based on Timeframe and Cell Data

Here's a pseudocode version of what he was saying:

What he wrote...

counter = 0

loop rows {
    if something {
        add to message
        increment counter
    }
}

if counter {
    send message to one person
}

What you need to write:

counter = 0

loop rows {
    if something {
        make message
        send message to person in row
    }
}

You will need to access the data from the email column as well. I'll leave the rest to you to work out, except to say that you should read this article on how to get the data out of your sheet efficiently.

Best Answer

Related Solutions

Google Sheets – How to Make a Query That Checks for a Date

Google Sheets – Sending Email Notifications Based on Timeframe and Cell Data

Related Topic