SUMIF Range in Range

问题:

I'm trying to sum only if a value from a collection is in a set list of values.

I've tried using a SUMIFbut it keeps returning 0.

Given:

and

and

I've Tried:

=SUM(SUMIF(M7:M10,X2:X4,H7:H10))

But it's returning 0 each time. How can I modify this to correctly SUMIF

回答1:

Or just use SUMPRODUCT()

=SUMPRODUCT(SUMIFS(H:H,M:M,X2:X4))

回答2:

One way is to add 3 sumifs:

=SUMIF(M7:M10,X2,H7:H10))+SUMIF(M7:M10,X3,H7:H10))+SUMIF(M7:M10,X4,H7:H10))

回答3:

You can use an array formula, enter:

=SUM(SUMIF(M7:M10,X2:X4,H7:H10))

and Press CTRL+SHIFT+ENTER (or COMMAND+RETURN on the Mac) to enter the formula

https://support.microsoft.com/en-gb/help/275165/when-to-use-a-sum-if-array-formula