This is a tough tough formula. Normally I can find a way around these
but for this I can't. Any help would be greatly appreciate. I need
one formula and only one formula for this situation. I am trying to do
a count with several conditions. Normally I could use the sumproduct
formula for this and my formula looked like this:

=sumproduct((a4:a10000=3)*(h4:h10000="A")*(k4:k100 00=""))

That was fine but now I have a situation where a given field within a
column can be equal to different values and still need to be counted.
That really doesn't make sense but let me try and show you an example
based on what I tried to do. I wanted to add one sumproduct to another
sumproduct but it wasn't giving me the correct number. Here was the
formula I tried:

=sumproduct((a4:a10000=3)*(h4:h10000="A")*(o4:o100 00<"EXP")*(o4:o10000<"")*(q4:q10000="NOSCRN"))+s umproduct((a4:a10000=3)*(h4:h10000="A")*(k4:k10000 ="")*(q4:q10000<"EXP")*(Q4:Q10000<"NOSCRN"))

I didn't know if you could add sumproduct formulas together within one
sumproduct formula but this way didn't work. I can't do one sumproduct
formula because you'll notice the Q column in one situation needs to be
equal to NOSCRN but in another it cannot equal NOSCRN.

Based on this situation, is there a way I can do this with one formula?
My only other solution has been creating a separate sheet with 1's and
0's based off of an if(AND() statement and then doing a countif() on
the main formula page. I guess what i'm looking for in the end is the
ability to add both of these sumproduct formulas together so if the
first portion counted to 5 and the second portion counted to 10, the
cell would total to 15.

I'd love to find a solution to this problem. I've run out of ideas.

Thanks


--
ski2004_2005
------------------------------------------------------------------------
ski2004_2005's Profile: http://www.excelforum.com/member.php...o&userid=16418
View this thread: http://www.excelforum.com/showthread...hreadid=277871

Hi
you could add SP formulas. So your formula should do. But you may try:
=sumproduct(--(a4:a10000=3),--(h4:h10000="A"),--((o4:o10000<"EXP")*(o4
:o10000<"")*(q4:q10000="NOSCRN")+")*(k4:k10000="" )*(q4:q10000<"EXP")*
(Q4:Q10000<"NOSCRN")0))

--
Regards
Frank Kabel
Frankfurt, Germany

"ski2004_2005" schrieb im
Newsbeitrag ...

This is a tough tough formula. Normally I can find a way around
these
but for this I can't. Any help would be greatly appreciate. I need
one formula and only one formula for this situation. I am trying to
do
a count with several conditions. Normally I could use the sumproduct
formula for this and my formula looked like this:

=sumproduct((a4:a10000=3)*(h4:h10000="A")*(k4:k100 00=""))

That was fine but now I have a situation where a given field within a
column can be equal to different values and still need to be counted.
That really doesn't make sense but let me try and show you an example
based on what I tried to do. I wanted to add one sumproduct to
another
sumproduct but it wasn't giving me the correct number. Here was the
formula I tried:


=sumproduct((a4:a10000=3)*(h4:h10000="A")*(o4:o100 00<"EXP")*(o4:o10000
<"")*(q4:q10000="NOSCRN"))+sumproduct((a4:a10000= 3)*(h4:h10000="A")*(k
4:k10000="")*(q4:q10000<"EXP")*(Q4:Q10000<"NOSCR N"))

I didn't know if you could add sumproduct formulas together within
one
sumproduct formula but this way didn't work. I can't do one
sumproduct
formula because you'll notice the Q column in one situation needs to
be
equal to NOSCRN but in another it cannot equal NOSCRN.

Based on this situation, is there a way I can do this with one
formula?
My only other solution has been creating a separate sheet with 1's
and
0's based off of an if(AND() statement and then doing a countif() on
the main formula page. I guess what i'm looking for in the end is
the
ability to add both of these sumproduct formulas together so if the
first portion counted to 5 and the second portion counted to 10, the
cell would total to 15.

I'd love to find a solution to this problem. I've run out of ideas.

Thanks


--
ski2004_2005
---------------------------------------------------------------------
---
ski2004_2005's Profile:
http://www.excelforum.com/member.php...o&userid=16418
View this thread:
http://www.excelforum.com/showthread...hreadid=277871