# Forumla not returning any results

✭✭

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

## Best Answer

• ✭✭
Answer ✓

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

## Answers

• ✭✭✭✭✭

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

• ✭✭

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?

• ✭✭✭✭✭✭

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

• ✭✭

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

• ✭✭✭✭✭✭

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?

• ✭✭

Hi Paul,

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

• ✭✭✭✭✭✭

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?

• ✭✭

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

• ✭✭✭✭✭✭

=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)?

• ✭✭
Answer ✓

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

• ✭✭✭✭✭✭

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!