SUMIF statement based on dates

I would like to create a SUMIF equation to only sum data (column F) based on a date range (column . It works in excel with the following equation for reference.

=SUMIF($B$9:$B$2000, ">=9/1/2018", $F$9:$F$2000)-SUMIF($B$9:$B$2000, ">=9/30/2018", $F$9:$F$2000)

However, when I import it to smartsheets, I get an error.

My best attempt at the new equation is (also creating an error):

=SUMIF(Date:Date, AND(@cell >= DATE(2018, 9, 22), @cell < DATE(2018, 9, 28))), Onboarding:Onboarding)

Could someone help me with how the equation should be formatted?

Thanks,

DeAnna

Find more posts tagged with

Formulas

Comments

Mike Wilday

Sumif can't use AND statements. Just rewrite it as a SUMIFS which allows you to have multiple criterion. Try this:

=SUMIFS(Onboarding:Onboarding, Date:Date, >=DATE(2018, 9, 22), Date:Date, <DATE(2018, 9, 28))

https://help.smartsheet.com/function/sumifs

J. Craig Williams

"SUMIF can't use AND" is incorrect.

I don't use SUMIF (preferring always to use SUMIFS even for 1 criterion situations)

Here is the use in SUMIFS

=SUMIFS(Onboarding:Onboarding, Date:Date, AND(@cell >= DATE(2018, 9, 22), @cell < DATE(2018, 9, 28)))

Craig

Mike Wilday

Really? I have tried using ANDS in Countifs and Sumifs multiple times. That is intriguing. Did they recently fix it? Or does it not work in countifs but does work in sumifs?

Becky Swanwick

I have a similar query to the one above and had got as far as figuring I needed a SUMIFS formula. However, I'm not quite following your solution, Craig -- to what does @cell refer? I guess I may be complicating things because mine involves referring to another sheet, so I need to introduce ranges -- but essentially it's the same query as the original poster's. Any help you can give would be much appreciated!

J. Craig Williams

@cell reference

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Becky Swanwick

Thank you. I think my difficulty is now that I want to refer to two date columns. So I want cells to sum where the date in the start column is after a particular date but the date in the finish column is before a particular date. Does that make sense?

J. Craig Williams

Something like this should work

=SUMIFS({range to sum}, {date range 1}, @cell >= DATE(2018, 9, 22), {date range 2}, @cell < DATE(2018, 9, 28))

This can be on the same Sheet or (as shown here) on a different Sheet with x-references, as long as the 3 ranges are the same size.

You don't want to use the AND because the range for each criterion is different (location, not size)

Craig

Becky Swanwick

Hi Craig, a belated thanks for this -- very helpful!

J. Craig Williams

You are welcome

Craig

SophiaJKhan

Hi Craig, I may be late to this discussion, but I am struggling with two things, one is the Sumifs with criteria referencing another sheet, as well as the @cell feature.

I have 2 sheets for PTO tracking accrual. The first is a table with every date of the year. In the corresponding line I am trying to use the following formula:

=SUMIFS({Approved PTO Hours}, {PTO Start Date}, Date2, {Employee Name}, "NAME")

So basically I am taking the range from another sheet which has all approved PTO stored in it, then criteria 1 is matching the date column in the Approved PTO to the date on the respective line, criteria 2 validates the employee name matches. This way if there is any approved PTO for a respective date and employee on the Approved PTO sheet, it should pull in on the line with the date and employee on the master data file.

What am I doing wrong with the formula?

THANK YOU!

Paul Newcome

@SophiaJKhan You formula appears to be correct. How are you creating your cross sheet references? Have you double checked to ensure columns and data are of the correct type? Are you able to provide screenshots with sensitive/confidential data removed, blocked, or replaced with "dummy data"?

Sign in to join the conversation!

Quick Links

Trending discussions

How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
count if value is between a date range (reference between 2 sheets)
Hi team i have 2 sheets for material inventory. the first is to record witch equipment is ship out for repair and the other is the total of equipment available. the first is set as follow: equipment type, date out, date return, quantity (negative number) the other one is as follow: each row is for a single date with the…