This is the calculation for my range names: This one is the CNSDAY. They are
all the same, just the column is changed to appropriate column
=OFFSET(cnsdlywrksht!$D$2,0,0,COUNTA(cnsdlywrksht! $D:$D),1)

"T. Valko" wrote:

can't figure out why it won't work
Range names are dynamic

The first thing I would look at is the formulas that define the dynamic
ranges. Make sure they all return ranges of the *same size*.


--
Biff
Microsoft Excel MVP


"Tasha" wrote in message
...
ok, this formula was working on last month's sheet, can't figure out why
it
won't work on this months? Hoping someone can help??

Range names are dynamic for Sheet 2 (CNSDAY,CNSDPT,CNSHSV)
FOR CNSDAY, E2 EQUALS THE DAY OF THE MONTH....E2,F2, ETC.

I want to pull all CNSDPT 11, that match the day of the month(CNSDAY) in
E2,
and also have a CNSHSV of MED, SUR, etc., then if that all matches, Sum
CNSQTY

On sheet 1, this is my formula:
=SUMPRODUCT(--(CNSDPT=11),--(CNSDAY=E$2)--(ISNUMBER(MATCH(CNSHSV,{"MED";"SUR";"SBA";"IOB"},0 ))),CNSQTY)

I have changed the way the formula was put in several different ways and
either get a #N/A! or #VALUE! error? What am I doing wrong?