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

--
Stef

Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Stefan" wrote in message
...
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

--
Stef

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


"Arvi Laanemets" wrote:

Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Stefan" wrote in message
...
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

--
Stef