SUMPRODUCT help again

[Tasha]

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?

 

[T. Valko]

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

 

[Tasha]

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]

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.

 

[Tasha]

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]

OK, good deal. Thanks for the feedback!