IF AND OR COUNTIF help needed 1

[knifey]

Hi,
I'm trying (without much success) to build a rather complex formula. The first part marked in green is working.

What I've got so far:
=IF(AND(master!C3:C2002="Rx",master!Q3:Q2002="NA"),COUNTIFS(master!C3:C2002,"Rx",master!Q3:Q2002,"NA",master!N3:N2002,""),IF(OR(AND(master!C3:C2002="Rx",master!Q3:Q2002<>"NA",master!N3:N2002,""),AND(master!C3:C2002="Rx",master!Q3:Q2002<>"NA",master!R3:R2002,"")),COUNTIF(master!R3:R2002,"")))

What this should be doing (in plain English):
IF column C = "Rx" AND column Q = "NA" THEN
Count blanks in Column N
ELSEIF column C = "Rx" AND column Q <> "NA" AND column N = "" OR _
column C = "Rx" AND column Q <> "NA" AND column R = "" THEN
Count blanks in Column N or R
ENDIF

I could do this quite easily in VBA but my new boos is a formula freak!
Any advice or pointers would be much appreciated.

K

 

[SkipVought]

hi,

This might work for you.
[tt]
=SUMPRODUCT((Master!C2:C2001="Rx")*(Master!Q2:Q2001="NA")*(Master!N2:N2001=""))+
SUMPRODUCT((Master!C2:C2001="Rx")*(Master!Q2:Q2001<>"NA")*(Master!N2:N2001=""))+SUMPRODUCT((Master!C2:C2001="Rx")*(Master!Q2:Q2001<>"NA")*(Master!R2:R2001=""))
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Or this may be better...
[tt]
=SUMPRODUCT((Master!C2:C2001="Rx")*(Master!Q2:Q2001="NA")*(Master!N2:N2001=""))+
SUMPRODUCT((Master!C2:C2001="Rx")*(Master!Q2:Q2001<>"NA")*OR(Master!N2:N2001="",Master!R2:R2001=""))
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[byundt]

If you only want to count a row once (assuming the columns C and Q criteria are met), then consider the following formula:
=SUMPRODUCT((Master!C2:C2001="Rx")*(Master!Q2:Q2001="NA")*(Master!N2:N2001=""))+
SUMPRODUCT((Master!C2:C2001="Rx")*(Master!Q2:Q2001<>"NA")*(((Master!N2:N2001="")+(Master!R2:R2001=""))>=1))

In the previous comments, the first suggested formula will double count whenever column C is Rx, column Q is not NA and both columns N and R are blank.

In the previous comments, the second suggested formula will count a row if column C is Rx and column Q is not NA no matter what columns N and R contain--as long as there is at least one empty cell somewhere in either column N or R.

 

[knifey]

Thanks Byundt,
Your a star. I can't believe I havn't come across the sumproduct function before. It would have saved me so much time in producing summary figures!
Cheers,
Roy