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?

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?

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?

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?

well, not sure what was wrong, I deleted all the formulas from those cells,
copy/pasted from another table I had set up, changed to the range names I
needed and it worked? ??? Thanks for your help though....just glad it is
working now...!!!

"T. Valko" wrote:

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?