Formula searching data that dose not equal two criteria

F

FarmerGemGem

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!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
 
B

bj

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
 
Top