On 2002-09-19 10:52, pgzr wrote:

Dave, thanks for the input.

In answer to your question, I have 6 named ranges that are referred to in my formula.

I just tried the SUMPRODUCT formula you suggested, but it doesn't seem to do the trick, either. I'm wondering if I need to tweak the Criteria that the formula is looking at, rather than the formula.

Let me clarify what I mean. Taking "Control" as an example, the value of cell F14 can be Profit or Non-Profit. My "control" range contains cells with the value of either Profit or Non-Profit.

If I'm asked to report all the Profit data, and I enter "Profit " in F14, my formula works fine. Same goes if I'm asked for Non-Profit data, and I enter "Non-Profit" in F14. However, if I'm asked for all the data, regardless of Profit or Non-Profit, I'm not sure what to put in F14 to tell the formula "I don't care what value is in here, include the data anyway"? Or a way to tell the formula "If F14=All, ignore the control criteria altogether"?

The Control example is the simplest example in my case. I have to do the same thing with the "coun" criteria, which could be one of 88 values, or ALL of those values.

Am I doomed?

Original formula:

{=SUM(IF(type=F$11,IF(bedrange=F$12,IF(coun=F$13,IF(control=F$14,IF(peergrp=F$15,IF(HOSP_BASE=F$16,SUMMARY!$R$2:$R$1309/F$18)))))))}