Using COUNTIF function, Array formulas

[jmusilli11]

I have column E filled with a random assortment of 1s and 2s from E3:E34. I will be entering a random assortment of 1s and 2s into D3:D34. I want to take the cells in Column E that match the cells in Column D and sum them in E35. As of right now, I use an array formula that is as follows: {=COUNTIF(E3:E34,$D3:$D34)}. When I insert the formula into the E35, it totals the number of cells in column E that match D3, instead of checking each cell to see if it matches its counterpart in Column D. Is there something that I'm doing wrong? Thanks!

[jason.b75]

You ask for SUM but you're using a COUNT function?

=SUMPRODUCT(--(E3:E34=D3:D34))

=SUMPRODUCT(--(E3:E34=D3:D34),E3:E34)

Do either of those give the result you're looking for?

[Glenn Kennedy]

Use this normal formula:


=SUMPRODUCT(--($D$3:$D$34=$E$3:$E$34))

[jmusilli11]

It was to my understanding that the COUNT function would be used for counting the number of cells that meet the criteria, and that the SUM function would sum the cell values. Could you explain what the difference is between the two functions please? Those formulas also did the trick, thanks!

[Glenn Kennedy]

If you were SUMMING matching pairs... a pair of 1s would count as 1 and a pair of 2s as two. You are counting matching pairs, whereby any matching pair counts as 1.

[jason.b75]

Your understanding is correct, but this line quoted from your original question

I want to take the cells in Column E that match the cells in Column D and sum them in E35.

suggested that you wanted to sum the values, rather than count the number of matches.

From the 2 formulas that I suggested, the first will count the matching rows, the second will sum the values that match.

[jmusilli11]

Ah, I see where that would be confusing. Yeah, I wanted to count the matching rows, not sum their values. Regardless, your formula did the trick, so thank you!