I have a worksheet with 3 columns, as follows:
A: File Number
B: Master Number
C: Value $
There are multiple occurrences of each file number for each master number; the $ value for each file number is the same on each instance of the file number.
What I need to do is sum the $ value of the master number, but only for each unique file number.
In the sample data above, I need the values for the first instances of the file number in Col A, totaled by the master number in Col B.
For instance, the total for master number 3910189075 in Col B =
However, in the case with master number 3910195070, because there are TWO unique file numbers with the same $ value, in sequence, the above formula only picks up the first occurrence of the $ value, which of course skews the totals being calculated.
Can anyone help me with this formula to sum the $ value based first on the master number, and secondly on the unique file number therein? I realize this is a rather convoluted question, but I'm not sure of the best way to show/phrase it.
A: File Number
B: Master Number
C: Value $
There are multiple occurrences of each file number for each master number; the $ value for each file number is the same on each instance of the file number.
What I need to do is sum the $ value of the master number, but only for each unique file number.
HTML:
COL A COL B COL C
10305 3910195070 $5,266.92
10305 3910195070 $5,266.92
10306 3910195070 $5,266.92
10306 3910195070 $5,266.92
10307 3910195070 $14,846.13
10307 3910195070 $14,846.13
10307 3910195070 $14,846.13
10307 3910195070 $14,846.13
10307 3910195070 $14,846.13
10276 3910189075 $2,880.40
10276 3910189075 $2,880.40
10276 3910189075 $2,880.40
10276 3910189075 $2,880.40
10276 3910189075 $2,880.40
10277 3910189075 $280.42
10277 3910189075 $280.42
10277 3910189075 $280.42
10277 3910189075 $280.42
In the sample data above, I need the values for the first instances of the file number in Col A, totaled by the master number in Col B.
For instance, the total for master number 3910189075 in Col B =
In this case, a formula that only extracts the first occurrence of a value in Col C is sufficient:$2880.40 + $280.42 = $3160.82
Code:
=SUM(IF(FREQUENCY(C:C,C:C)>0,C:C)
However, in the case with master number 3910195070, because there are TWO unique file numbers with the same $ value, in sequence, the above formula only picks up the first occurrence of the $ value, which of course skews the totals being calculated.
Can anyone help me with this formula to sum the $ value based first on the master number, and secondly on the unique file number therein? I realize this is a rather convoluted question, but I'm not sure of the best way to show/phrase it.