adding two sumproduct formulas together

[ski2004_2005]

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

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

That was fine but now I have a situation where a given field within
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 exampl
based on what I tried to do. I wanted to add one sumproduct to anothe
sumproduct but it wasn't giving me the correct number. Here was th
formula I tried:

=sumproduct((a4:a10000=3)*(h4:h10000="A")*(o410000<>"EXP")*(o410000<>"")*(q4:q10000="NOSCRN"))+sumproduct((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 on
sumproduct formula but this way didn't work. I can't do one sumproduc
formula because you'll notice the Q column in one situation needs to b
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 an
0's based off of an if(AND() statement and then doing a countif() o
the main formula page. I guess what i'm looking for in the end is th
ability to add both of these sumproduct formulas together so if th
first portion counted to 5 and the second portion counted to 10, th
cell would total to 15.

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

Thank

 

[Frank Kabel]

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

--
Regards
Frank Kabel
Frankfurt, Germany

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:k10000=""))

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")*(o410000<>"EXP")*(o410000