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

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
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…
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…