conditional SUM errors

[kysaul]

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

[NBVC]

Try:

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

[kysaul]

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.

[NBVC]

are you sure that each of those named ranges reference actual existing ranges?...check each of those named ranges through Insert|Name|Define.

Also are there any of those errors within those ranges already?

[kysaul]

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?

[NBVC]

If you would post the workbook, it would be easier to see what's going on..

Usually the #REF! error means the reference doesn't exist...

[daddylonglegs]

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.......

[NBVC]

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

That might also be a clue... In Excel pre-2007, you cannot use whole column references such as I:I.. but usually this give #NUM! error...

[kysaul]

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

[daddylonglegs]

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)

[kysaul]

Thanks for the reply. Unfortunately both columns A and I are not numeric, so the count form doesn't work. If I fill in all the blanks and set the range, rResults, to its exact length then everything works.So I think we're on the right track. But with an array formula, if there's a blank, doesn't that produce an error on evaluation?

[daddylonglegs]

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?

[kysaul]

Neither column will contain blanks.rTests is not numeric, rPapDone is a date, which I believe is treated as numeric.So should I base the Offset function on that column(column D)?

[daddylonglegs]

Yes, you should probably define rResults as

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

and rTests can be the same but substitute $I$3 with the correct column

[kysaul]

Great, that actually works. When I run through the "evaluate formula", the comparison with a blank field evaluates to 'false', not an error, so when the array comparison occurs no error is generated. In summary, to define a dynamic column I have to base the Offset function on a numeric column which has no blanks.
Thank you for persevering with this.

[daddylonglegs]

No problem...... it's certainly simpler when there are no blanks but if you use COUNTA instead of COUNT then the column can be text or numbers (or dates) or a combination of the two, it doesn't matter