Forum Discussion
Help Understanding a Formula
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...
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.
Sergei - thank you for the breakdown. I guess I didn't understand them as well as I thought. Your explanation was perfect. Much appreciated.