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

Hi,

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

=SUM(IF(A1:A999="
A",IF(D1:D999="Dd",IF(F1:F999="1",IF(E1:E999="1", 1,0)))))

I am trying to get no of records with
" A" values in Column A,
"Dd" 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,

Try this:

=SUMPRODUCT((A1:A999="A")*(D1:D999="Dd")*(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.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"CT" wrote in message
...
Hi,

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

=SUM(IF(A1:A999="
A",IF(D1:D999="Dd",IF(F1:F999="1",IF(E1:E999="1", 1,0)))))

I am trying to get no of records with
" A" values in Column A,
"Dd" 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,

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")*(D1:D999="Dd")*(F1:F999 ="1")*(E1:E999="1"))

Jerry

CT wrote:

Hi,

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

=SUM(IF(A1:A999="
A",IF(D1:D999="Dd",IF(F1:F999="1",IF(E1:E999="1", 1,0)))))

I am trying to get no of records with
" A" values in Column A,
"Dd" 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,

No need to array enter it

--
Regards,

Peo Sjoblom


"Jerry W. Lewis" wrote in message
...
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")*(D1:D999="Dd")*(F1:F999 ="1")*(E1:E999="1"))

Jerry

CT wrote:

Hi,

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

=SUM(IF(A1:A999="
A",IF(D1:D999="Dd",IF(F1:F999="1",IF(E1:E999="1", 1,0)))))

I am trying to get no of records with " A" values in Column A,
"Dd" 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,

The OP's formula

=SUM(IF(A1:A999="A",IF(D1:D999="Dd",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")*(D1:D999="Dd")*(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 wrote:

No need to array enter it

I see that now, thanks for the clarification

--
Regards,

Peo Sjoblom


"Jerry W. Lewis" wrote in message
...
The OP's formula

=SUM(IF(A1:A999="A",IF(D1:D999="Dd",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")*(D1:D999="Dd")*(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 wrote:

No need to array enter it