Hi All
I need to set up a target calculator for sales. the Targets for 2016 = Actual GP in 2015 + £100,000 - Any GP from accounts Lost in 2015
Please refer to tab Data in attachment. when you filter for salesperson D, there are 2 accounts indicated as "N" in column AC. "N" = those accounts whose GP needs to be excluded to arrive at target for 2016. But instead of only subtracting the total GP to get the annual target, I also need to subtract the GP for those accounts from their monthly target.
So for Salesperson D, in Jan-15, the total GP for these 2 accounts is £6900 - this result needs to be displayed in Cell B7 in the Calculator Tab. For Feb 15, the total GP to be excluded is £11,956. This result is to be displayed in Cell C7 and so on.
I tried using a SUMPRODUCT formula as per below but I am getting a #VALUE! error - cannot understand where I am going wrong so looking for some assistance.
=SUMPRODUCT((dATA!$D$2:$AC$26)*(dATA!$C$2:$C$26=$A$2)*(dATA!$AC$2:$AC$26="N"))*(TEXT(dATA!$D$1:$AC$1,"mmm-yy")=B$4&"-15")
Thanks for your help!
Bookmarks