Summing values from a column based on match in another column and first distinct occurrence of value in a third column

I'm trying to figure out a formula-based solution to sum the values in a column based on matching a value in another column but only for distinct occurrences of values in a third column.

Here's a simplified sample spreadsheet: I need to match the letter in column F (A,B,C) to the "Letters" column B, and then sum the value in "Values" column D once per distinct number in "Numbers" column C and display that sum in column G "Sum of Values".

The correct sum is displayed in the cells of column G but I don't have a formula to accomplish this. Any help would be appreciated!

• Generally you won't get good answers if you ask for a complete solution. You need to have a plan and some code for people to look at. You might have better luck on Mr. Excel: mrexcel.com – HackSlash Apr 10 '18 at 22:16

For this kind of problem, it helps to think in terms of arrays.

If you can get an array (list) of the numbers in Values (column D) where Letters (column B) equals "A" and Numbers has duplicates removed, you could just sum the array to get the answer.

This expression:

(B\$2:B\$12=F2)

gives an array of True/False values with True wherever column B = "A". This one:

(C\$2:C\$12<>C\$3:C\$13)

gives an array of True/False values with True wherever a cell in column C is not equal to the following cell. Because your duplicates are in sequential cells (comment below if this will not always be the case), this array has False where the extra occurrences of a value are, and, essentially, it filters out the duplicates. Multiplying these two arrays together:

(B\$2:B\$12=F2)*(C\$2:C\$12<>C\$3:C\$13)

converts True/False values to 1's and 0's, and gives an array with 1 in the positions that we want to be in the sum. Using this array as the logical_test in an IF(), and column D as the value_if_true:

IF((B\$2:B\$12=F2)*(C\$2:C\$12<>C\$3:C\$13)>0,D\$2:D\$12)

returns an array of the Values in column D wherever there was a 1, interspersed with False wherever there was a zero. Now we can just sum the array. This formula, filled down from G2 gives the results shown below.

=SUM(IF((B\$2:B\$12=F2)*(C\$2:C\$12<>C\$3:C\$13)>0,D\$2:D\$12))

Note that this is an array formula and must be entered with CTRLShiftEnter. • This is a great (and functional) solution and I appreciate the depth on explaining the mechanics of multiplying arrays to test multiple criteria! – Robert Brett Apr 11 '18 at 15:00
• Great! Glad to help. – Bandersnatch Apr 11 '18 at 15:38

If you are able to add columns to your data, the following will work for the simple example given:

1. Add a formula to concatenate the letters and numbers. I have used column A for this formula, i.e. Cell A2: =B2&C2
2. Drag this formula down to apply to cells A2:A12
3. Add a formula to test whether each concatenation of letters and numbers is the first unique concatenation. I have used column E for this formula, i.e Cell E2: =COUNTIF(A\$2:A2,A2)
4. Drag this formula down to apply to cells E2:E12
5. Use the following formula in H2 to sum the values where the letter matches, but only for the first concatenation of letter and number, i.e. Cell G2: =SUMIFS(\$D\$2:\$D\$12,\$B\$2:\$B\$12,F2,\$E\$2:\$E\$12,1)
6. Drag this formula down to apply to cells G2:G4
• This works well and will keep it in mind! Thank you. – Robert Brett Apr 11 '18 at 15:01