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?