sumproduct problem

S

Stefan

Hi all. I am trying to count the number of occurrences of a set of
conditions in my table, but with one of the criteria including 2 conditions.
My current formula is:
=SUMPRODUCT(--(H3:H50="FLINDERS"),--(J3:J50="MODERATE"),--(B3:B50="STRAT
6"),--(K3:K50={"ALLOCATED","CONSIDER"}))

However, this produces a #value answer. If I simplify it to just have one
condition in the last set (e.g.
=SUMPRODUCT(--(H3:H50="FLINDERS"),--(J3:J50="MODERATE"),--(B3:B50="STRAT
6"),--(K3:K50="ALLOCATED"))
everything works fine and it will return the number of rows that meet this
criteria.
How can I make it work so that it will count rows that have "allocated" or
"consider" as the third condition?
Help appreciated - it's been driving me nuts!!
Oh for a 'not equal to' sign on the keyboard or in excel!!!
Thanks in advance to anyone who can help.
Steve
 
A

Arvi Laanemets

Hi

=SUMPRODUCT(--(H3:H50="FLINDERS"),--(J3:J50="MODERATE"),--(B3:B50="STRAT
6"),(K3:K50="ALLOCATED")+(K3:K50="CONSIDER"))
 
S

Stefan

Thanks Arvi - works a treat!!
Steve
--
Stef


Arvi Laanemets said:
Hi

=SUMPRODUCT(--(H3:H50="FLINDERS"),--(J3:J50="MODERATE"),--(B3:B50="STRAT
6"),(K3:K50="ALLOCATED")+(K3:K50="CONSIDER"))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top