Filtering out data from an equation

bhope51

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

 

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.