Count the occurence of multiple (4 conditions). - Need urgent help

[Guest]

Hi,

I am unable to get the correct count using the formula :

=SUM(IF(A1:A999="
A",IF(D1999="D>d",IF(F1:F999="1",IF(E1:E999="1",1,0)))))

I am trying to get no of records with
" A" values in Column A,
"D>d" values in Column D,
"1" values in Column F,
"1" values in Column E

and Sum all the counts- after applying the nested filters.


Can someone help me in debugging the same?
Will appreciate early response!

Thanks in advance,

 

[Ragdyer]

Try this:

=SUMPRODUCT((A1:A999="A")*(D1999="D>d")*(E1:E999=1)*(F1:F999=1))

I made an assumption that the 1's in Column E and F were real numbers, and
didn't need the quotes.

If I guessed wrong, and they are text, just add the quotation marks.

 

[Jerry W. Lewis]

You would have to array enter (Ctrl-Shift-Enter) this formula for it to
work. A simpler approach would use the fact that you can coerce TRUE
into 1 and FALSE into 0, so that the following formula should work and
does not require array entry:

=SUMPRODUCT((A1:A999="A")*(D1999="D>d")*(F1:F999="1")*(E1:E999="1"))

Jerry

 

[Peo Sjoblom]

No need to array enter it

 

[Jerry W. Lewis]

The OP's formula

=SUM(IF(A1:A999="A",IF(D1999="D>d",IF(F1:F999="1",IF(E1:E999="1",1,0)))))

does require array entry to work, as I tried to say. I presume lack of
array entry is why CT could not get it to work.

My alternative formula

=SUMPRODUCT((A1:A999="A")*(D1999="D>d")*(F1:F999="1")*(E1:E999="1"))

does not require array entry, as I did say.

On rereading my previous reply, the object referred to by "this formula"
in my first sentence is not clear, and should probably have read "your
formula".

Jerry

 

[Peo Sjoblom]

I see that now, thanks for the clarification