conditional SUM errors

I am using a SUM function with multiple conditions as an array formula.

       =SUM((YEAR(rPapDone)=T6)*(rTests="Colposcopy")* (rResults="LGSIL"))

One of the tested ranges has blank cells, leading to errors (#REF). I added an error catching function,

    =SUM((YEAR(rPapDone)=T6)*(rTests="Colposcopy")*(IFERROR((rResults="Negative"),0)))

but this logically always evaluates to 0. How do I deal with blank cells in an array formula? Thanks

No difference. The error occurs when evaluating the named range "rResults", or if I change the condition, other columns that have blanks, not columns which don't. So I assumed that that was the problem.

Yes. The named range is defined in the Name Manager
[code]
rResults=OFFSET(PAPS!$I$3,0,0,COUNT(PAPS!$I:$I),1)
[code]
There are no errors in the column.
Is that the problem , the dynamic range? If there are blanks is the range only equal to the number of cells with contents, rather than to the last entry?

I'd suggest that you try NBVC's SUMPRODUCT but using specific ranges to eliminate (or not) dynamic ranges as the cause of your problem, e.g. try something like

=SUMPRODUCT((YEAR(A2:A1000)=T6)*(B2:B1000="Colposcopy")*(C2:C1000="LGSIL"))

Note that the YEAR function applied to any cell which isn't truly blank or numeric will return an error.

If you get a result with that formula then possibly the dynamic ranges are the issue.......

Sorry for the delay. I'm using Excel 2007, so I think the reference to columns should be okay. Substituting SumProduct makes no difference. Filling in the blank dates solves the problem, so I think the blanks with an array formula is the issue. Posting the file is problematic since it contains medical information. I'm not sure how to deal with blanks , since there are going to be some as data is added to the worksheet. Any thoughts? Thanks

Do you have any columns which don't have blanks? If so then you can base the dynamic ranges on that column. This also helps in that it ensures that all the ranges are the same size, whatever column they are in....., e.g.

if column A has continuous data with no blanks the make the column I range

=OFFSET(PAPS!$I$3,0,0,COUNT(PAPS!$A:$A),1)

even if all columns have blanks it's still best to base all dynamic ranges on a single column. If there are blanks at the end, however, you need to be careful that you base dynamic ranges on the one with the furthest reach.

With blanks you can use a formula like this for the dynamic range

=PAPS!$I$3:INDEX(PAPS!$I:$I,MATCH(9.99999999999999E+307,PAPS!$A:$A))

Notice, again, that it's the column I range but based on the last value in column A (assuming column A has numeric values)

If column I isn't numeric then you can't define the dynamic range with the formula

=OFFSET(PAPS!$I$3,0,0,COUNT(PAPS!$I:$I),1)

because COUNT only counts numbers not text and will therefore evaluate to zero, causing your #REF! error. At the very least you need to change COUNT to COUNTA as COUNTA will count text and numbers.....

...but if you have blanks in column I then that still won't get the correct range (I assume the blanks are truly blank, not containing formulas which return blanks?), because, as you remarked earlier, the size of the dynamic range will be based on the number of values within it rather than the last value.

Do rTests and rPapdone ranges contain blanks.....Are these ranges numeric, what columns are they?