Total number of inquiries is off by one

Laura Walker

I am fairly new to Smartsheets and racking my brain trying to solve this problem and I am hoping the community can help!

I have a log sheet that we use to keep track of inquiries received through one of our channels. I have the 'Inquiry type' column, that has the multiple dropdown option for all the different types, and then some other columns, such as 'Action date', etc. I am trying to condense this data, so I can use it on a Dashboard, but this is where I run into a problem.

When I calculate a total number of all inquiries, I get a total of 196, which is correct.
However, when I create a formula to count different types of inquiries by months, I get 195.

For 'Total number of inquiries', I use the formula following formula for each inquiry type:

=COUNTIF([Inquiry Type]:[Inquiry Type], CONTAINS("Inquiry Type 1", @cell))

and then I just SUM these up.

Then, when I am calculating inquiry types by month, I want to separate all the inquiries in just 2 groups, e.g. GROUP 1 = 'Inquiry Type 1' and GROUP 2 = all other inquiry types.
So I use the following formula for GROUP 1:

=COUNTIFS([Action Date]:[Action Date], >=DATE(2025, 1, 1), [Action Date]:[Action Date], <=DATE(2025, 1, 31), [Inquiry Type]:[Inquiry Type], "Inquiry Type 1")

and this formula for GROUP 2:

=COUNTIFS([Action Date]:[Action Date], >=DATE(2025, 1, 1), [Action Date]:[Action Date], <=DATE(2025, 1, 31), [Inquiry Type]:[Inquiry Type], <>"Inquiry Type 1", [Inquiry Type]:[Inquiry Type], ISBLANK(@cell ) = false)

When summing up the inquiries by months, the first formula returns the same amount for 'Total number of Inquiry Type 1' as the 'Total number of inquiries' for this type does, however, the last formula for all the other inquiry types returns 1 too few. Any ideas why?

All help is greatly appreciated!

Laura Walker

Ugh, I found the issue! One row had a dropdown option selected, but no action date, so that wasn't being counted in the total count. I can't believe I missed that! Good lesson for next time, doublecheck the obvious things first 😑

Thanks again, Brian!

Brian Wilson DC

@Laura Walker I was able to get the following Countifs Statement to work. See if this captures your missing date. I used the entire year to make sure it was capturing the right data. Just adjust the dates to the months you want. Please note, I essentially made an "AND" statement for the Inquiry Type column stating that it must not be equal to Inquiry Type 1 and it must not be blank.

=COUNTIFS([Action Date]:[Action Date], >=DATE(2025, 1, 1), [Action Date]:[Action Date], <=DATE(2025, 12, 31), [Inquiry Type]:[Inquiry Type], AND(@cell<>"Inquiry Type 1", ISBLANK(@cell ) = false))

I hope this helps!

Regards,

Brian

Laura Walker

@Brian Wilson DC thank you for responding and helping with this!
Unfortunately, after adjusting the Action date span, I still get one too few as a total result. I am not sure why it's not counting one single instance apparently, and I don't know how to determine which one isn't being counted.

I appreciate the help nonetheless, though!

Best,
Laura

Brian Wilson DC

@Laura Walker Is it possible to just post that data in this thread? I don't mind combing through it to see if I can find out the incorrect cell. Just hide all of the other cells except for the inquiry type row and the dates and I'll take a look.

Laura Walker

@Brian Wilson DC Unfortunately, I am not able to do that. But I sincerely appreciate the offer to help comb through it!

Laura Walker

Ugh, I found the issue! One row had a dropdown option selected, but no action date, so that wasn't being counted in the total count. I can't believe I missed that! Good lesson for next time, doublecheck the obvious things first 😑

Thanks again, Brian!