SUMIFS, cross-sheet reference based on dates.

Sorry if this is a duplicate. I couldn't find an answer that fit my problem..

Formula: =SUMIFS({AccHours}, {ShipDate}, <=Date3, {completed}, <>"x")

All references are from another sheet except Date3. Date3 is a formula as well, which is TODAY()+2. This works in Excel, but doesn't seem to work here. I don't get an error, but instead I get zero. I still get zero even if I change the Date3 reference to a date instead of a formula.

Find more posts tagged with

Manufacturing

Formulas

IT & Operations

Project Management

Comments

Paul Newcome

What do you have in rows 4 and 5? Based on your screenshots, row 3 SHOULD be 0.

ajsmith

Those rows are not used yet. The row with the latest July date should be greater than zero.

Paul Newcome

That's what I am asking. What is the formula in the row with the latest July date?

ajsmith

Oh, sorry.

=SUMIFS({AccHours}, {ShipDate}, <=Date4, {completed}, <>"x")

Date4 is the July date next to the column. Does that help?

Paul Newcome

It does. Thank you.

Now... What all is included in the {AccHours} reference?

I also see that the first two rows in the Silencers column are linked out to somewhere. Do they play into any of this at all?

ajsmith

I actually named that reference wrong. It should be referencing the column Silencers on the other sheet.

The formula is supposed to do this:

Sum the hours in the silencer column if the ship date is less than or equal to the date in the left column (Date3). Then, if the Completed column has an x in it, don't count it because it's been shipped.

=SUMIFS({SilHours}, {ShipDate}, <=Date3, {completed}, <>"x")

Paul Newcome

Ok. I am going to suggest that you start by double checking the ranges. You are wanting to reference the entire Silencers column, but only the first two rows are linked as opposed to the entire column.

It may be that one or more ranges are off.

Paul Newcome

That also leads me to ask... Where is the Accessories column linked out to?

I am also curious to see the far right side of the Completed column. It doesn't look like that column is linked out to anywhere, but it could be that your screenshot just trimmed that edge of the cell off.

ajsmith

I changed the reference to the entire columns for each item, but it still didn't work. I've attached updated screenshots.

Thanks so much for taking the time to look at this!

Paul Newcome

How are each of the dates populated and what column types are they in?

How are your totals populated that you are wanting to sum and what type of column are they in?

ajsmith

EDIT: I took out the reference to the "x" and it worked. Looks like I have a problem with that reference.

The dates are formulas. Today's is using TODAY(). The one below is using that result + 1. However, I've tried changing them to solid dates instead of a formula and that didn't work.

Date column is date format. The totals and the hours to total are Text/Number format.

Paul Newcome

Try using an @cell reference...

@cell <> "x")

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?