Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Filtering out data from an equation
Hello,
Is there a way to filter out specific data items from an equation?
Specifically I am creating a sumifs formula and I would like to be able to filter out fiscal year data without making it a condition of the formula, that way we wont have to change the formula every year. I have a column that shows the fiscal year as part of the data set. But not part of the formula. Below is the formula.
=SUMIFS([Total With Service Charge (18%) if Applicable]:[Total With Service Charge (18%) if Applicable], [Batch Billing Account]:[Batch Billing Account], =CONSVC19, [Manager Approval]:[Manager Approval], =1, [Internal Processing Status]:[Internal Processing Status], ="Service Complete", [Pick-up Date]:[Pick-up Date], MONTH(@cell) = 1)
Any help would be greatly appreciated.
Regards,
Brian Hope
Comments
-
could you put the fiscal year somewhere at the top of your sheet and reference it in your formula?
-
I tried that with the following formula and received a #INVALID DATA TYPE response
=SUMIFS([Total With Service Charge (18%) if Applicable]:[Total With Service Charge (18%) if Applicable], [Batch Billing Account]:[Batch Billing Account], =CHRTR19, [Manager Approval]:[Manager Approval], =1, [Internal Processing Status]:[Internal Processing Status], ="Service Complete", [Pick-up Date]:[Pick-up Date], MONTH(@cell) = 10, [Fiscal Year]:[Fiscal Year], =Month19)
But if I remove the Fiscal Year part it works fine. Just as a reference, the values in the Fiscal Year column is 2017 - 2018 and 2018 - 2019. With the value in Month19 being 2017 - 2018.
