I'm trying to sum up the values in column K where the value in column X
equals 2021, 2022, 2023, & 2025. Am I doing it wrong?

=SUMPRODUCT(--(Main!$F$2:$F$5000=DATE(YEAR(A14),MONTH(A14),DAY( A14))),--(Main!$F$2:$F$5000<=DATE(YEAR(B14),MONTH(B14),DAY( B14))),--(Main!$X$2:$X$5000="2021"),--(Main!$X$2:$X$5000="2022"),--(Main!$X$2:$X$5000="2023"),--(Main!$X$2:$X$5000="2025"),Main!$K$2:$K$5000)

If A14 and B14 are dates you can eliminate the DATE function and just refer
to the cells themselves. Also, when you enclose numbers in quotes like this:

--(Main!$X$2:$X$5000="2021")

Excel evaluates them as TEXT. So, in the above expression the formula is
looking for the TEXT string 2021. If the values in that range are really
numbers text "2021" and numeric 2021 won't match.

Try this:

=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)

--
Biff
Microsoft Excel MVP


"Secret Squirrel" wrote in
message ...
I'm trying to sum up the values in column K where the value in column X
equals 2021, 2022, 2023, & 2025. Am I doing it wrong?

=SUMPRODUCT(--(Main!$F$2:$F$5000=DATE(YEAR(A14),MONTH(A14),DAY( A14))),--(Main!$F$2:$F$5000<=DATE(YEAR(B14),MONTH(B14),DAY( B14))),--(Main!$X$2:$X$5000="2021"),--(Main!$X$2:$X$5000="2022"),--(Main!$X$2:$X$5000="2023"),--(Main!$X$2:$X$5000="2025"),Main!$K$2:$K$5000)

Thanks for your help. Those values are in fact text since that's the way they
are extracted from my database. If I was to just add the "" around them
within your formula it will still work, correct?

Thanks for pointing out the date function also. I didn't realize I left it
set up like that since I was pulling the data a different way in an earlier
version of this worksheet.



"T. Valko" wrote:

If A14 and B14 are dates you can eliminate the DATE function and just refer
to the cells themselves. Also, when you enclose numbers in quotes like this:

--(Main!$X$2:$X$5000="2021")

Excel evaluates them as TEXT. So, in the above expression the formula is
looking for the TEXT string 2021. If the values in that range are really
numbers text "2021" and numeric 2021 won't match.

Try this:

=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)

--
Biff
Microsoft Excel MVP


"Secret Squirrel" wrote in
message ...
I'm trying to sum up the values in column K where the value in column X
equals 2021, 2022, 2023, & 2025. Am I doing it wrong?

=SUMPRODUCT(--(Main!$F$2:$F$5000=DATE(YEAR(A14),MONTH(A14),DAY( A14))),--(Main!$F$2:$F$5000<=DATE(YEAR(B14),MONTH(B14),DAY( B14))),--(Main!$X$2:$X$5000="2021"),--(Main!$X$2:$X$5000="2022"),--(Main!$X$2:$X$5000="2023"),--(Main!$X$2:$X$5000="2025"),Main!$K$2:$K$5000)

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)


--
Biff
Microsoft Excel MVP


"Secret Squirrel" wrote in
message ...
Thanks for your help. Those values are in fact text since that's the way
they
are extracted from my database. If I was to just add the "" around them
within your formula it will still work, correct?

Thanks for pointing out the date function also. I didn't realize I left it
set up like that since I was pulling the data a different way in an
earlier
version of this worksheet.



"T. Valko" wrote:

If A14 and B14 are dates you can eliminate the DATE function and just
refer
to the cells themselves. Also, when you enclose numbers in quotes like
this:

--(Main!$X$2:$X$5000="2021")

Excel evaluates them as TEXT. So, in the above expression the formula is
looking for the TEXT string 2021. If the values in that range are really
numbers text "2021" and numeric 2021 won't match.

Try this:

=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)

--
Biff
Microsoft Excel MVP


"Secret Squirrel" wrote in
message ...
I'm trying to sum up the values in column K where the value in column X
equals 2021, 2022, 2023, & 2025. Am I doing it wrong?

=SUMPRODUCT(--(Main!$F$2:$F$5000=DATE(YEAR(A14),MONTH(A14),DAY( A14))),--(Main!$F$2:$F$5000<=DATE(YEAR(B14),MONTH(B14),DAY( B14))),--(Main!$X$2:$X$5000="2021"),--(Main!$X$2:$X$5000="2022"),--(Main!$X$2:$X$5000="2023"),--(Main!$X$2:$X$5000="2025"),Main!$K$2:$K$5000)

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

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