Using sumifs to return date value in a date value column

✭✭✭

I need help with the formula for returning the date text from a date column if certain criteria is met.

=SUMIFS(Date526:Date1045, Day526:Day1045, "MON", [WK #]526:[WK #]1045, [Miss Unavoidable]2616)

The formula seems to work but doesn't return a value in the cell.

• ✭✭✭✭✭✭

@mromaire I don't think SUMIFS is the way to go here.

So you want the value from the Date column between rows 526 and 1045, where the DAY column = "MON", and the WK # = the value in [Miss Unavoidable]2616, right?

INDEX/COLLECT will be your best bet:

=INDEX(COLLECT(Date526:Date1045, Day526:Day1045, "MON", [WK #]526:[WK #]1045, [Miss Unavoidable]2616), 1)

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

@mromaire I don't think SUMIFS is the way to go here.

So you want the value from the Date column between rows 526 and 1045, where the DAY column = "MON", and the WK # = the value in [Miss Unavoidable]2616, right?

INDEX/COLLECT will be your best bet:

=INDEX(COLLECT(Date526:Date1045, Day526:Day1045, "MON", [WK #]526:[WK #]1045, [Miss Unavoidable]2616), 1)

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭

Jeff,

That didn't work. I'm getting #INVALID COLUMN VALUE.

Thanks for looking into this.

• ✭✭✭✭✭✭
edited 03/22/23

@mromaire IF you are trying to collect a date value, the formula needs to be placed in Date-type column. Otherwise, you need to convert it to the proper type.

Can you share a screenshot of your data and sheet structure, along with the types of the columns involved?

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭

Jeff,

I take it back. It is working. I copied to the wrong cell.

Thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!