1

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?

Improve this question
1
  • It's just a string that I am testing
    – Skeleton Bow
    Commented Aug 5, 2018 at 19:22

1 Answer 1

Reset to default
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))

Improve this answer
2
  • 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
    Commented Aug 5, 2018 at 20:53
  • @SkeletonBow I see, check my answer for update
    – user0
    Commented Aug 5, 2018 at 23:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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