Formula searching data that dose not equal two criteria

[Guest]

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",Data!D220,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

 

[T. Valko]

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

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

Biff

 

[Sandy Mann]

Gem

Try:

=SUMPRODUCT((F2:F20<>"Y")*(G2:G20<>"O"),D220)

Not array entered.

--
HTH

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

an alternate equation woul dbe
=sumproduct(--(Data!F2:F20="<>Y"),--(Data!G2:G20="<>O"),Data!D220)
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

 

[Guest]

Thanks Biff and Sandy.
The formula worked great.

Thanks very much.
Gemma