Filtering and Find difference

[rithamworld]

Hi

I have the data in the above of the sheet. If i filter the Name as A then it will shows the Difference between Feb14 - Jan 14 (subtraction) and Difference Mar 14 - Feb 14.

If i select the Name B then it will shows the difference of Feb 14 - Jan 14 of B and so on.

I am attaching the sample for your reference

[FDibbins]

Im not sure (yet), how to run the calcs based on the middle month, but for the 1st and last month calc, try this...

=INDEX($C$1:$C$14,MATCH(SUBTOTAL(104,$D$1:$D$14),$D$1:$D$14,0))-INDEX($C$1:$C$14,MATCH(SUBTOTAL(105,$D$1:$D$14),$D$1:$D$14,0))

[FDibbins]

OK no, that wont work, I need to take another look

[Tony Valko]

Maybe like this...

Add this column header to E1: Difference

Enter this array formula in E3 and copy down to E13:

=IF(SUBTOTAL(2,C$2:C3)=1,"",(INDEX(C$2:C$13,MATCH(SUBTOTAL(3,C$2:C2),SUBTOTAL(3,OFFSET(C$2:C$13,,,ROW(C$2:C$13)-ROW(C$2)+1)),0))-INDEX(C$2:C$13,MATCH(SUBTOTAL(3,C$2:C2)+1,SUBTOTAL(3,OFFSET(C$2:C$13,,,ROW(C$2:C$13)-ROW(C$2)+1)),0)))*-1)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Apply the filter as desired. The difference will be displayed in column E.

For the grand total use:

=AGGREGATE(9,5,E3:E13)

[kvsrinivasamurthy]

Pl see attached file.Filter not necessary. Select From dropdown.