SumProduct

[Secret Squirrel]

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)

 

[T. Valko]

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)

 

[Secret Squirrel]

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]

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)

 

[Rick Rothstein \(MVP - VB\)]

Those values are in fact text

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

 

[T. Valko]

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

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

 

[Rick Rothstein \(MVP - VB\)]

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,2025})*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

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

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

 

[T. Valko]

Yes, the multiplication form does work but the ISNUMBER(MATCH(...)) method
is better. I haven't done a calc time test, though.

In the (...)*(...) form you're evaluating each element (cell_ref) of the
array against *all* 4 variables. This returns 4 intermediate results for
each element.

The ISNUMBER(MATCH(...)) method evaluates each element of the array against
*any* of the variables and returns only a single intermediate result for
each element. So, logic tells me that this should be more efficient.

--
Biff
Microsoft Excel MVP

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,2025})*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

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

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

 

[Peo Sjoblom]

It is also better to use the built in comma delimited way of SUMPRODUCT
when possible since it ignores text in the summarize range, Rick's formula
will return value errors if there is for instance blanks derived from "" or
any other text value in Main!$K$2:$K$5000. While there might not be many
direct text entries in a range that needs to be summarized it is not far
fetched to expect it can contain formulas like

=IF(ISNA(VLOOKUP),"",VLOOKUP)

or

IF(X="","",X)


--

Regards,

Peo Sjoblom

Yes, the multiplication form does work but the ISNUMBER(MATCH(...)) method
is better. I haven't done a calc time test, though.

In the (...)*(...) form you're evaluating each element (cell_ref) of the
array against *all* 4 variables. This returns 4 intermediate results for
each element.

The ISNUMBER(MATCH(...)) method evaluates each element of the array
against *any* of the variables and returns only a single intermediate
result for each element. So, logic tells me that this should be more
efficient.

--
Biff
Microsoft Excel MVP

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,2025})*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

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


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

 

[Rick Rothstein \(MVP - VB\)]

Yes, the multiplication form does work but the ISNUMBER(MATCH(...)) method

is better. I haven't done a calc time test, though.

In the (...)*(...) form you're evaluating each element (cell_ref) of the
array against *all* 4 variables. This returns 4 intermediate results for
each element.

The ISNUMBER(MATCH(...)) method evaluates each element of the array
against *any* of the variables and returns only a single intermediate
result for each element. So, logic tells me that this should be more
efficient.

Peo's comments (which make for a compelling argument for "knowing" your data
before constructing formulas to analyze it) notwithstanding, I would be
interested in seeing such a time test. Although I understand your logic, my
experience with other languages tells me that straight comparisons are
almost always much faster than calls into a function plus that function's
code execution (and here, you are executing two separate function calls);
plus, one would think that underneath the MATCH function call is a series of
comparison operations not too dissimilar to those being done in the
modification I proposed to your formula (although true, the MATCH function's
code would be at compiled code speed whereas the formula comparisons would
be at interpreted code speed). I'm thinking that in the end, more than
likely, you are probably right, but seeing a time test would be interesting.

Rick

 

[T. Valko]

Peo's comments (which make for a compelling argument for "knowing" your
data before constructing formulas to analyze it) notwithstanding, I would
be interested in seeing such a time test. Although I understand your
logic, my experience with other languages tells me that straight
comparisons are almost always much faster than calls into a function plus
that function's code execution (and here, you are executing two separate
function calls); plus, one would think that underneath the MATCH function
call is a series of comparison operations not too dissimilar to those
being done in the modification I proposed to your formula (although true,
the MATCH function's code would be at compiled code speed whereas the
formula comparisons would be at interpreted code speed). I'm thinking that
in the end, more than likely, you are probably right, but seeing a time
test would be interesting.

Rick

Calc times screencap:

http://img110.imageshack.us/img110/5883/calctimeshm8.jpg

As you can see the the larger the range the more "significant" the
difference.

Calc timer code (thanks to Charles Williams):

http://msdn2.microsoft.com/en-us/library/aa730921.aspx