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

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

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

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

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