0

I am trying to use the SUMIF function, but with a different array sizes for the range and sum ranges.

If a value appears in either columns A or B, I would like to sum the corresponding value in column C. This is the formula I tried using:

=SUMIF(A:B, "MYVALUE", C)

What I expected to happen was that it would find MYVAULE in A2 and B4, and then add cells C2 and C4 together.

However, it actually sums C2 and D4 together (and essentially trying to make the sum range the same size as the range, even though I ask it to use only C as the sum range).

There is a way to do this using a sum of SUMIF functions:

=SUMIF(A:A, "MYVALUE", C) + SUMIF(B:B, "MYVALUE", C)

This would work the way I want it to, but it wouldn't be nice if I was trying to search many columns for MYVALUE.

Is there a better way to do this in one formula?

  • It's just a string that I am testing – Skeleton Bow Aug 5 '18 at 19:22
1

you can use =SUMIFS where you can refer to whole range for criteria instead of column/row like in =SUMIF

=SUMIFS(A1:B10;A1:B10;"=5")


or you can use =SUM with =FILTER

=SUM(FILTER(C:C, A:A = 3),
     FILTER(C:C, B:B = 3))

  • I apologize if I was unclear, but I am not trying to add the original range (in this case, the number 5), but I am trying to add values corresponding to the number 5s which are in the C column. The second longer formula I wrote in my post does what I am looking for, although it's quite verbose – Skeleton Bow Aug 5 '18 at 20:53
  • @SkeletonBow I see, check my answer for update – user0 Aug 5 '18 at 23:30

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.