CODEWITHSUNDEEP
google-sheets
Ionut
Ionut

Reputation: 1749

google sheets sumifs with range or named range as criteria

I'm trying to sum all the values for the items defined in the NameRange, using SUMIF: =SUMIF(E1:E10,D1:D10,NamedRange)

So I would expect to have 1+3+7+8+10 =29, basically all in yellow.

But my result is 0. What do I do wrong?

Here is an editable example

enter image description here

Upvotes: 2

Views: 1976

Answers (2)

Max Makhrov
Max Makhrov

Reputation: 18707

Try this:

=ArrayFormula(SUMIF(G2:G11;NamedRange;H2:H11))

The result is auto-axpanded row with sums by named range elements "A, B, C, D".

Next step is to use sum:

=sum(ArrayFormula(SUMIF(G2:G11;NamedRange;H2:H11))) enter image description here

Upvotes: 2

JPV
JPV

Reputation: 27262

That's not gonna work I'm afraid. But see if this does:

=sumproduct(regexmatch(D1:D10, join("|", NamedRange)), E1:E10)

Upvotes: 1

Related Questions