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 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
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.
