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

Find more posts tagged with

Forms

Sheets

Formulas

Comments

rjudenberg

could you put the fiscal year somewhere at the top of your sheet and reference it in your formula?

bhope51

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.