OK, so you need to make sure that this portion of each of those formulas
returns the same number:

COUNTA(cnsdlywrksht!$D:$D)

When using multiple dynamic ranges based on a common range it's best to base
the height/width variable on a "primary key" column/row.

--
Biff
Microsoft Excel MVP


"Tasha" wrote in message
...
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?