SUMPRODUCT over muliple ranges issue

This maybe a question already asked but I have not been able to word it properly to do a search.

I am fully aware that SUMPRODUCT can be refered to as a select statement and have been using this for some time as such.

However, I have hit an instance where I only need to trigger the sum under mutlple conditions.

The base statement is:-

SELECT SUM(Quantity) FROM Orders WHERE Gencode="GEN0368"

and the resulting sumproduct is:-

=SUMPRODUCT((C2:C32) * (B2:B32="GEN0368"))

The hard bit is to make the sum product appear only when another condition is triggered.

Doing so via a single criteria was easy enough with:-

=SUMPRODUCT((C2:C32) * (B2:B32="GEN0368") * (J2:J32="W3019100582"))

But I need that second criteria to be a range, this is where I am coming up stuck.

Any ideas?

Answers


Try this

=SUMPRODUCT(C2:C32,(B2:B32="GEN0368")*ISNUMBER(MATCH(J2:J32,Your_range,0)))

where Your_range can be an actual range (single column or row), or an array entered like this {"abc","xyz","stu"}


Need Your Help

Ruby Validation failed on .create

ruby-on-rails validation

I am attempting to use a .create method but constantly getting back the error:

Separate srcdir and objdir with vim and gcc

vim gcc make

When I'm working in vim, my current working directory (./) contains all my source. I build in an objdir, let's call it ./builddir/. When I build from vim, using makeprg=make\ -C\ builddir, the comp...