Sum Product and Subtotal

[tomb242]

Hello. I am working on a retention report and have created a Sum product formula counting new people who appeared this. This works well.

=SUMPRODUCT( --(Table1[2017] =1), --(Table1[2016]=0))

My problem is creating a subtotal for after a filter/slicer being applied. I have scoured these boards and when I think I have cracked it I get errors for too few or too many arguments.

This is how it sits at present having too few arguments and to be honest I am not sure how to proceed.

=SUMPRODUCT( --(Table1[2014] =1), --(Table1[2013]=0), SUBTOTAL(9, OFFSET(--(Table1[2014] =1), --(Table1[2013]=0)))

Any advice/clues/pointers gratefully received. I am using Excel 2016.

Thanks in advance.

 

[tomb242]

I am working on a retention report and have created a Sum product formula counting new people who appeared this year (missed that out and cannot edit the post!)