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!