Forumla not returning any results

ElissaM

Hi

I have hit a wall with this formula. The intent is to count the number of attendees where the audiece type =Sellers AND the Status colum is Blue. The dates in am interested in are Nov 1 20201 - December 31st. I have data in my spreadsheet that matches the criteria. I have used the exact date construct in other formulas and it works. I have also used the Status =Blue as criteria in other formulas and it works as well

Smartsheet accepts the formula hence I believe it has been written correctly I am just not geting the data

=SUMIFS ({APJ Enablement Tracker Attendees}, {APJ Enablement Tracker Audience}, @cell, "Sellers", {APJ Enablement Tracker Status}, "Blue", {APJ Enablement Tracker Date}, >=DATE(2021, 11, 01), {APJ Enablement Tracker Date}, <=DATE(2021, 12, 31))

Appreciate any help with this

Thanks

Elissa

ElissaM

It is working :)

Your formula worked. I then added the audience criteria to the end and I started seeing results

For some reason the audience criteria needed to be last??

Below is the working formula

=SUMIFS({APJ Enablement Tracker Attendees}, {APJ Enablement Tracker Status}, @cell = "Blue", { APJ Enablement Tracker Date}, @cell >= DATE(2021, 11, 1), {APJ Enablement Tracker Date}, @cell <= DATE(2021, 12, 31), {APJ Enablement Tracker Audience}, @cell = "Sellers")

Thank you so much for your help Paul

BullandKhmer

Here is my stab in the dark!

=SUMIFS ({APJ Enablement Tracker Attendees}, {APJ Enablement Tracker Audience}, ="Sellers", {APJ Enablement Tracker Status}, ="Blue", {APJ Enablement Tracker Date}, >=DATE(2021, 11, 01), {APJ Enablement Tracker Date}, <=DATE(2021, 12, 31))

ElissaM

Hi BullandKhmer,

Thank you for the reply. Unfortunatly using the = sign did not make any differnce, the formula is still not returning any values.

I expect the forumula to add the total sum of all attendees and return a value - Is this expection correct?

Paul Newcome

What exactly is in the Attendees column on the APJ Enablement Tracker sheet?

ElissaM

Hi Paul, Numbers are in the Attendees column eg 25. These numbers are manually entered. Colum Properties shown below. I have checked every cell in the Attendees Column to ensure it has a number

Paul Newcome

Ok. Lets take a look at the other ranges then and their data. Have you applied a filter to the sheet that mimics these ranges and criteria to see how many rows come up there?

ElissaM

Hi Paul,

Yes, I have created a filter that mimics my formula (see below). It displayed the correct output.

Paul Newcome

Ok. What column type is the Status column and how exactly is it populated? Are you able to provide screenshots of the source data for reference?

ElissaM

The first image is the colum properties of the status Column. The second is the date and the third is an extrat of the data showing the date, status=blue and attendees (manually entered number)

Thanks Paul

Paul Newcome

=SUMIFS({APJ Enablement Tracker Attendees}, {APJ Enablement Tracker Status}, @cell = "Blue", {APJ Enablement Tracker Date}, @cell >= DATE(2021, 11, 01), {APJ Enablement Tracker Date}, @cell <= DATE(2021, 12, 31))

What happens if we remove the Audience bit (above)?

ElissaM

It is working :)

Your formula worked. I then added the audience criteria to the end and I started seeing results

For some reason the audience criteria needed to be last??

Below is the working formula

=SUMIFS({APJ Enablement Tracker Attendees}, {APJ Enablement Tracker Status}, @cell = "Blue", { APJ Enablement Tracker Date}, @cell >= DATE(2021, 11, 1), {APJ Enablement Tracker Date}, @cell <= DATE(2021, 12, 31), {APJ Enablement Tracker Audience}, @cell = "Sellers")

Thank you so much for your help Paul

Paul Newcome

Sometimes there is a bit of a bug in the back-end. I have had to rearrange range/criteria sets when dealing with dates on numerous occasions.

Glad it is working for you now though.