Sum Ifs Formula with dates

Help please! :)

I am wanting to sum the total quotes done on a job, when the builders name equals "Classic Builders WLGTN" and the date the quote was after 1 January 2021 but it is not working.

Any guidance would be much appreciated!

=SUMIFS({QCM 2020 Range 1}, {QCM 2020 Onwards 2}, "Classic Builders WLGTN", {QCM 2020 Date 4}, (@cell >= DATE(2021, 1, 1)))

Thank you and have a great weekend!

Tracey

Find more posts tagged with

Accepted answers

Tracey Tume

Thanks for your suggestion Kelly but it hasn't worked. It was one of the options I had tried... pulling my hair out now ha ha

This is what I did after your suggestion but coming up as zero amount when there is actually 171

=SUMIFS({QCM 2020 Range 1}, {QCM 2020 Onwards 2}, "Classic Builders WLGTN", {QCM 2020 Date 4}, @cell >= DATE(2021, 1, 1))

Thanks

Tracey

All comments

Kelly Moore

Hey @Tracey Tume

Try this

=SUMIFS({QCM 2020 Range 1}, {QCM 2020 Onwards 2}, "Classic Builders WLGTN", {QCM 2020 Date 4}, @cell >= DATE(2021, 1, 1))

Tracey Tume

Thanks for your suggestion Kelly but it hasn't worked. It was one of the options I had tried... pulling my hair out now ha ha

This is what I did after your suggestion but coming up as zero amount when there is actually 171

=SUMIFS({QCM 2020 Range 1}, {QCM 2020 Onwards 2}, "Classic Builders WLGTN", {QCM 2020 Date 4}, @cell >= DATE(2021, 1, 1))

Thanks

Tracey

Kelly Moore

Hey @Tracey Tume

For trouble shooting purposes, let's begin to remove the individual pieces. I added the ISDATE criteria in case your date field isn't all dates.

=SUMIFS({QCM 2020 Range 1}, {QCM 2020 Date 4}, ISDATE(@cell), {QCM 2020 Date 4}, @cell >= DATE(2021, 1, 1))

What does this give you?

Tracey Tume

Hi Kelly

Your enquiries made me check the properties of the columns and the "so called" DATE column was a drop down (long stores), so I have change the range to another true date column and it has worked! YAY!

=SUMIFS({QCM 2020 Range 1}, {QCM 2020 Onwards 2}, "Classic Builders WLGTN", {QCM 2020 Date 4}, @cell >= DATE(2021, 1, 1))

thanks for your quick responses I can leave work for the week and not be mulling it over :)

have a great weekend

Tracey

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.