Formula searching data that dose not equal two criteria
 

Hi. Can anyone help me, please??

I have a range of data on sheet 'Data'.
On another sheet, I have formula to count records that don't equal Y in
column F, and don't equal O in column G.
I have tried using an array formula.
{=SUM(IF(Data!F2:F20="<Y",IF(Data!G2:G20="<O",Da ta!D2:D20,0),0))}
I think this means that if the cell doesn't contain a Y, it looks to see if
the next column contains an O. If both values are true(not a Y or an O), then
the numbers in column D and added. If the values are false(either one of the
columns contains a Y or O) then the formula returns a zero.

However, it does not work as it keeps returning a zero, when I know it
should be returning a value.

Please help if you can. If you need any more info, just ask.
Thanks
Gem

Formula searching data that dose not equal two criteria
 

Try it like this (normally entered, not an array):

=SUMPRODUCT(--(Data!F2:F20<"Y"),--(Data!G2:G20<"O"),Data!D2:D20)

Biff

"FarmerGemGem" wrote in message
...
Hi. Can anyone help me, please??

I have a range of data on sheet 'Data'.
On another sheet, I have formula to count records that don't equal Y in
column F, and don't equal O in column G.
I have tried using an array formula.
{=SUM(IF(Data!F2:F20="<Y",IF(Data!G2:G20="<O",Da ta!D2:D20,0),0))}
I think this means that if the cell doesn't contain a Y, it looks to see
if
the next column contains an O. If both values are true(not a Y or an O),
then
the numbers in column D and added. If the values are false(either one of
the
columns contains a Y or O) then the formula returns a zero.

However, it does not work as it keeps returning a zero, when I know it
should be returning a value.

Please help if you can. If you need any more info, just ask.
Thanks
Gem

Formula searching data that dose not equal two criteria
 

Gem

Try:

=SUMPRODUCT((F2:F20<"Y")*(G2:G20<"O"),D2:D20)

Not array entered.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"FarmerGemGem" wrote in message
...
Hi. Can anyone help me, please??

I have a range of data on sheet 'Data'.
On another sheet, I have formula to count records that don't equal Y in
column F, and don't equal O in column G.
I have tried using an array formula.
{=SUM(IF(Data!F2:F20="<Y",IF(Data!G2:G20="<O",Da ta!D2:D20,0),0))}
I think this means that if the cell doesn't contain a Y, it looks to see
if
the next column contains an O. If both values are true(not a Y or an O),
then
the numbers in column D and added. If the values are false(either one of
the
columns contains a Y or O) then the formula returns a zero.

However, it does not work as it keeps returning a zero, when I know it
should be returning a value.

Please help if you can. If you need any more info, just ask.
Thanks
Gem

Formula searching data that dose not equal two criteria
 

an alternate equation woul dbe
=sumproduct(--(Data!F2:F20="<Y"),--(Data!G2:G20="<O"),Data!D2:D20)
alternate issues might be that there might be number vs text issues if the
data is extracted

try
=Data!F2="Y"
=Data!G2
=Data!D2
(or appropriate cells)
see if what you get is what you expect

"FarmerGemGem" wrote:

Hi. Can anyone help me, please??

I have a range of data on sheet 'Data'.
On another sheet, I have formula to count records that don't equal Y in
column F, and don't equal O in column G.
I have tried using an array formula.
{=SUM(IF(Data!F2:F20="<Y",IF(Data!G2:G20="<O",Da ta!D2:D20,0),0))}
I think this means that if the cell doesn't contain a Y, it looks to see if
the next column contains an O. If both values are true(not a Y or an O), then
the numbers in column D and added. If the values are false(either one of the
columns contains a Y or O) then the formula returns a zero.

However, it does not work as it keeps returning a zero, when I know it
should be returning a value.

Please help if you can. If you need any more info, just ask.
Thanks
Gem

Formula searching data that does not equal two criteria
 

Thanks Biff and Sandy.
The formula worked great.

Thanks very much.
Gemma

"T. Valko" wrote:

Try it like this (normally entered, not an array):

=SUMPRODUCT(--(Data!F2:F20<"Y"),--(Data!G2:G20<"O"),Data!D2:D20)

Biff

"FarmerGemGem" wrote in message
...
Hi. Can anyone help me, please??

I have a range of data on sheet 'Data'.
On another sheet, I have formula to count records that don't equal Y in
column F, and don't equal O in column G.
I have tried using an array formula.
{=SUM(IF(Data!F2:F20="<Y",IF(Data!G2:G20="<O",Da ta!D2:D20,0),0))}
I think this means that if the cell doesn't contain a Y, it looks to see
if
the next column contains an O. If both values are true(not a Y or an O),
then
the numbers in column D and added. If the values are false(either one of
the
columns contains a Y or O) then the formula returns a zero.

However, it does not work as it keeps returning a zero, when I know it
should be returning a value.

Please help if you can. If you need any more info, just ask.
Thanks
Gem