Help Understanding a Formula

[K M]

Hello Everyone - 

I found a formula that does exactly what I need, but I'm having a difficult time understanding how it's doing. 

Problem:
Find duplicates from Column A and sum corresponding numbers from Column E; find last entry of the duplicates and place the sum value. 

Column A	Column B	Column C	Column D	Column E	Column F	Formula
321936	Test Data	Test Data	6/17/2017	77.75	REG
321936	Test Data	Test Data	7/1/2017	56.00	REG
321936	Test Data	Test Data	7/15/2017	57.75	REG
321936	Test Data	Test Data	7/29/2017	49.75	REG
321936	Test Data	Test Data	8/12/2017	72.50	REG
321936	Test Data	Test Data	8/26/2017	78.00	REG
321936	Test Data	Test Data	9/9/2017	79.50	REG
321936	Test Data	Test Data	9/9/2017	0.25	AVWAG	471.5

I found the following formula - 

=IF(AND(COUNTIF(A$2:A2,A3)=COUNTIF(A:A,A2),COUNTIF(A:A,A3)>1,SUMIF(A:A,A2,E:E), " ")

The formula works perfectly, but I'm not understanding how it's finding the last duplicate in the series of values. 

Any help breaking down the formula would be great - I'd prefer to know what's going on with the formula before using it freely.

Best, 

Kyle

[#2]

Hi,

You may take shorter range for your formula, e.g.

=IF(AND(COUNTIF(A$2:A2,A2)=COUNTIF($A$1:$A$10,A2),COUNTIF($A$1:$A$10,A2)>1),SUMIF($A$1:$A$10,A2,$E$1:$E$10), "")

stay on cell in in ribbon Formulas->Evaluate Formula to see how it works step by step.

First COUNTIF(A$2:A2,A2) calculates how many times the value in A2 is repeated from first row till current one; second COUNTIF gives you how many such values in entire column A. If they are equal and third COUNTIF says you have more than one such value in column A (i.e. you ignore unique values) when you SUMIF column E for all such values in column A, otherwise return empty cell.

Sample on shorter data is attached.

[#3]

Sergei - thank you for the breakdown. I guess I didn't understand them as well as I thought. Your explanation was perfect. Much appreciated.