Merge rows with same color but different value

Question

Let's say I have 4 rows in 2 columns as below

      | Col 1 | Col 2 |
Row 1 | Blue  |  54   |
Row 2 | Blue  |  25   |
Row 3 | Red   |  21   |
Row 4 | Blue  |  65   |

What I want is for Excel to recognise the two columns as one. So I want Excel to give one output, which would be the following three columns

      | Col 1 | Col 2 | Col 3 |
Row 1 | Blue  |  3    |  144  |
Row 2 | Red   |  1    |  21   |

Where the first column should be the 'Variable'. The second column should be the number of times it is present. The third column should give the sum of the numbers next to the variable. I will be updating the data, so the formula should also work for new data.

So Far

So far I have been able to solve the issue with the first two columns. The first column is simply the variable so that I can write in. The second one I have done using the COUNTIF command. But the third one is where I am stuck?

How can I make Excel recognise that each row has a given value?

Answer 1

Following method will fix the issue:

• Enter this Array Formula in Cell D32, finish with Ctrl+Shift+Enter & fill down.

  {=IFERROR(INDEX($A$32:$A$35,MATCH(0,COUNTIF($D$31:D31,$A$32:$A$35),0)),"")}

• Formula in Cell E32:

  =IF(COUNTIF($A$32:$A$35,$D32)=0,"",COUNTIF($A$32:$A$35,$D32))

• Final Formula in Cell F32:

=IF(SUMIF($A$32:$A$35,D32,$B$32:$B$35)=0,"",SUMIF($A$32:$A$35,D32,$B$32:$B$35))

N.B.

Last 2 Formula have been wrapped with If to replace Zero with Blanks when not get values in successive rows.

Adjust cell references in the Formula as needed.

Answer 2

You can use SUMPRODUCT function to get the result.

For Count column:

=SUMPRODUCT(($A$1:$A$4=D1)*1)

For Sum column:

=SUMPRODUCT(($A$1:$A$4=D1)*$B$1:$B$4)