Yes, I get the #VALUE! error if I use your comma-separated form of the
SUMPRODUCT formula; however, if you use the multiplication form of it (which
is my personal preference), this formula...

=SUMPRODUCT((Main!$F$2:$F$5000=A14)*(Main!$F$2:$F $5000<=B14)*(Main!$X$2:$X$5000={2021,2022,2023,202 5})*Main!$K$2:$K$5000)

which uses the change I suggested, appears to produce the exact same answers
as the formula you posted; although I am still just a little too newly
returned to Excel after my long absences from it to understand why.

OT: Be on the look-out for the email I just sent to you.

Rick


"T. Valko" wrote in message
...
Can't the 3rd term inside your SUMPRODUCT ... be simplified to this...
--([Main.xls]Sheet1!$X$2:$X$5000={"2021","2022","2023","2025"})

No. The logic of ISNUMBER(MATCH(...)) is
OR(cell_ref={"2021","2022","2023","2025"})

--([Main.xls]Sheet1!$X$2:$X$5000={"2021","2022","2023","2025"})

Is the same as AND(cell_ref={"2021","2022","2023","2025"})

Since a single cell won't contain all of the variables you'll end up with
a #VALUE! error because the evaluated arrays are not the same size.

Also, on an "off topic" note...
Did you receive the private email I sent you on Sept 2nd?

No, I didn't. What address did you use? biffinpitt is a bogus address. A
good address is:

xl can help at comcast period net

Remove "can" and change the obvious.


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
Those values are in fact text
If I was to just add the "" around them within
your formula it will still work, correct?

Yes:

=SUMPRODUCT(--(Main!$F$2:$F$5000=A14),--(Main!$F$2:$F$5000<=B14),--(ISNUMBER(MATCH(Main!$X$2:$X$5000,{"2021","2022"," 2023","2025"},0))),Main!$K$2:$K$5000)

Can't the 3rd term inside your SUMPRODUCT, which is this...

--(ISNUMBER(MATCH([Main.xls]Sheet1!$X$2:$X$5000,{"2021","2022","2023","2025"}, 0)))

be simplified to this...

--([Main.xls]Sheet1!$X$2:$X$5000={"2021","2022","2023","2025"})

(since it would occur inside a SUMPRODUCT function)?

Also, on an "off topic" note...
Did you receive the private email I sent you on Sept 2nd?

Rick