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.

Find more posts tagged with

Accepted answers

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)

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.