CODEWITHSUNDEEP
excel
Meesha
Meesha

Reputation: 821

SUMPRODUCT with SUMIFS

=SUMIFS($B:$B*$N:$N,$N:$N,">=5",$N:$N,"<10")

I want to multiply 2 columns and sum all instances of it, conditioned only on one of the columns.The formula gives an error because I multiply in the first argument, which just should be a single number I guess. I tried using SUMPRODUCT too, but as column B does not have a condition, it doesn't work. Can anyone advice please. Thanks in advance.

Upvotes: 1

Views: 2044

Answers (2)

BrakNicku
BrakNicku

Reputation: 5991

The equivalent SUMPRODUCT formula for your SUMIFS attempt is:

=SUMPRODUCT(B:B,N:N,--(N:N>=5)*(N:N<10))

But you should avoid using full column references in SUMPRODUCT. If you can't limit range sizes, consider using dynamic named ranges.

Upvotes: 2

Balinti
Balinti

Reputation: 1534

try:

=SUMPRODUCT(B:B,IF(N:N>=5,IF(N:N<10,N:N,""),""))

with array ctrl+shift+enter

Upvotes: 1

Related Questions