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?
Best Answer
you can use
=SUMIFSwhere you can refer to whole range for criteria instead of column/row like in=SUMIFor you can use
=SUMwith=FILTER