Using sumifs to return date value in a date value column

mromaire

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.

Jeff Reisman

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

Jeff Reisman

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

mromaire

Jeff,

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

Thanks for looking into this.

Jeff Reisman

@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?

mromaire

Jeff,

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

Thank you so much!