Sum Ifs Formula with dates

Tracey Tume

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

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

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