Adding YEAR function to Sumproduct(Subtotal(

Can anyone tell me why the attached sample spreadsheet formula's will not return the correct values please.

I have highlighted the relevant cells in red. I am trying to Sum the Invoice Values by Year for the Filtered data.

Thank you.

If you use the evaluate function under Formulas on the ribbon and step through the calculation you will see your current formula ends up multiplying a year by the invoice values and thus you get the wrong figures.

See attached for amended formulas that do what you need.

BSB

BSB

I had tried to use the evaluation function on the source file which has>1100 records but should have tried it on the sample!

thanks for your help.

Yes, I can see why that would have not been easy to decipher the cause of the problem.

Happy to help.

BSB

Sorry to bother you again BSB.

Could you try the sheet again but remove the filter on Column K.
Doesn't seem to be summing all the values!
Thanks.

Apologies. Was rushing off to a meeting so didn't have time to check that my solution covered anything more than what was visible at the time.

See if the amended attachment does what you expect.

BSB

Superb! Thanks very much.

No problem. Happy to help

BSB