0

I have a tables that looks like the following:

enter image description here

I have multiple Item rows, and for each there are many Value columns, and a single Result. What I would like to do in my TOTAL is, for each Value column, if each Value X = Result, then count that.

For example, for Item 1, Value 1 = Result, so I count that towards that Value 1 total. Same for Item 2, but not Item 3 because they are different. Same for other Value columns. So as you can see, Value 1 total is 3, Value 2 total is 2, and Value 3 total is 4. In my actual worksheet there may be many items and values.

So how do I write a formula for this?

I could write a painful string of multiple IF statements like below, but I feel like there should be an easier way:

=IF(B2=$E2,1,0)+IF(B3=$E3,1,0)+IF(B4=$E4,1,0)+IF(B5=$E5,1,0)+IF(B6=$E6,1,0)

Is COUNTIF the way to go here? Then what is my condition going to be?

=COUNTIF(B2:B6,???)

1

Use SUMPRODUCT() which will iterate the ranges checking if they match. And if so the -- will cause the TRUE to be 1 and the FALSE to be 0. It will then sum the resultant array.

=SUMPRODUCT(--(B$2:B$6=$E$2:$E$6))

enter image description here

1
  • WOW neat, never knew about the -- notation. Thanks! – Sach Jan 18 '18 at 22:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.