SUMIFS, cross-sheet reference based on dates.

ajsmith

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. 

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