I want to sum the values in AF28:AF2000 but only if the following two
conditions are met:

First, only if $H$28:$H$2000="Secure CE Lead" and second, only if
$T$28:$T$2000="True". I am using the following formula which returns 0.00 in
error.

=SUMPRODUCT(--($H$28:$H$2000="Secure CE
Lead"),--($T$28:$T$2000="True"),AF28:AF2000)

When I apply a filter to columns H and T and filter on "Secure CE Lead" and
€œTrue€� I can manually sum the values in AF resulting in the correct value of
430. What am I doing wrong?

I believe this will work: (--($H$28:$H$2000="Secure CE
Lead"),--($T$28:$T$2000="True"),--($AF$28:$AF$2000))

Dave
--
Brevity is the soul of wit.


"Dave Shultz" wrote:

I want to sum the values in AF28:AF2000 but only if the following two
conditions are met:

First, only if $H$28:$H$2000="Secure CE Lead" and second, only if
$T$28:$T$2000="True". I am using the following formula which returns 0.00 in
error.

=SUMPRODUCT(--($H$28:$H$2000="Secure CE
Lead"),--($T$28:$T$2000="True"),AF28:AF2000)

When I apply a filter to columns H and T and filter on "Secure CE Lead" and
€œTrue€� I can manually sum the values in AF resulting in the correct value of
430. What am I doing wrong?

A couple of things, you can try

=SUMPRODUCT(--($H$28:$H$2000="Secure CE
Lead"),--($T$28:$T$2000=TRUE),AF28:AF2000)

instead since "True" looks for the text whereas TRUE would be a result
of lets say =A20

If that does not work then you must have either hidden characters in
H28:H2000 like extra spaces or the values in AF28:AF2000 are text.

But I assume you have totaled them when using a filter so that doesn't
seem to be the problem


Regards,

Peo Sjoblom


Dave Shultz wrote:
I want to sum the values in AF28:AF2000 but only if the following two
conditions are met:

First, only if $H$28:$H$2000="Secure CE Lead" and second, only if
$T$28:$T$2000="True". I am using the following formula which returns 0.00 in
error.

=SUMPRODUCT(--($H$28:$H$2000="Secure CE
Lead"),--($T$28:$T$2000="True"),AF28:AF2000)

When I apply a filter to columns H and T and filter on "Secure CE Lead" and
€œTrue€� I can manually sum the values in AF resulting in the correct value of
430. What am I doing wrong?

Dave,

A couple of possibilities.

First, if you want a sum, you should use the SUM function, not the
SUMPRODUCT function.

Second, the conditions need to be written using the "IF" function. You need
to add something for the "FALSE" outcome of each "IF". Since you are doing
a SUM, I suggest "0" as the "FALSE" result.

Third, I'm not sure what the "--" in front of the two conditions does for
you. I don't think they should be there.

Finally, be sure that you use <CTRL<SHIFT<ENTER (all pressed at the same
time) to enter the formula as an array formula rather than a standard
formula.

The formula should be:

=SUM(IF($H$28:$H$2000="Secure CE
Lead",If($T$28:$T$2000="True",AF28:AF2000,0),0))

Good luck!

David


"Dave Shultz" <Dave wrote in message
...
I want to sum the values in AF28:AF2000 but only if the following two
conditions are met:

First, only if $H$28:$H$2000="Secure CE Lead" and second, only if
$T$28:$T$2000="True". I am using the following formula which returns 0.00
in
error.

=SUMPRODUCT(--($H$28:$H$2000="Secure CE
Lead"),--($T$28:$T$2000="True"),AF28:AF2000)

When I apply a filter to columns H and T and filter on "Secure CE Lead"
and
"True" I can manually sum the values in AF resulting in the correct value
of
430. What am I doing wrong?

Good catch. The result of column T is not text as I assumed. I changed
€œTrue€� to TRUE and it worked. Thanks for your help.

One of the other responders suggested {=SUM(IF($H$28:$H$2000="Secure CE
Lead",IF($T$28:$T$2000=TRUE,AF28:AF2000,0),0))} which also works just fine.
Is there any advantage either way?


"Peo Sjoblom" wrote:

A couple of things, you can try

=SUMPRODUCT(--($H$28:$H$2000="Secure CE
Lead"),--($T$28:$T$2000=TRUE),AF28:AF2000)

instead since "True" looks for the text whereas TRUE would be a result
of lets say =A20

If that does not work then you must have either hidden characters in
H28:H2000 like extra spaces or the values in AF28:AF2000 are text.

But I assume you have totaled them when using a filter so that doesn't
seem to be the problem


Regards,

Peo Sjoblom


Dave Shultz wrote:
I want to sum the values in AF28:AF2000 but only if the following two
conditions are met:

First, only if $H$28:$H$2000="Secure CE Lead" and second, only if
$T$28:$T$2000="True". I am using the following formula which returns 0.00 in
error.

=SUMPRODUCT(--($H$28:$H$2000="Secure CE
Lead"),--($T$28:$T$2000="True"),AF28:AF2000)

When I apply a filter to columns H and T and filter on "Secure CE Lead" and
€œTrue€� I can manually sum the values in AF resulting in the correct value of
430. What am I doing wrong?

SUMPRODUCT is a tad faster.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Dave Shultz" wrote in message
...
Good catch. The result of column T is not text as I assumed. I changed
"True" to TRUE and it worked. Thanks for your help.

One of the other responders suggested {=SUM(IF($H$28:$H$2000="Secure CE
Lead",IF($T$28:$T$2000=TRUE,AF28:AF2000,0),0))} which also works just
fine.
Is there any advantage either way?


"Peo Sjoblom" wrote:

A couple of things, you can try

=SUMPRODUCT(--($H$28:$H$2000="Secure CE
Lead"),--($T$28:$T$2000=TRUE),AF28:AF2000)

instead since "True" looks for the text whereas TRUE would be a result
of lets say =A20

If that does not work then you must have either hidden characters in
H28:H2000 like extra spaces or the values in AF28:AF2000 are text.

But I assume you have totaled them when using a filter so that doesn't
seem to be the problem


Regards,

Peo Sjoblom


Dave Shultz wrote:
I want to sum the values in AF28:AF2000 but only if the following two
conditions are met:

First, only if $H$28:$H$2000="Secure CE Lead" and second, only if
$T$28:$T$2000="True". I am using the following formula which returns
0.00 in
error.

=SUMPRODUCT(--($H$28:$H$2000="Secure CE
Lead"),--($T$28:$T$2000="True"),AF28:AF2000)

When I apply a filter to columns H and T and filter on "Secure CE Lead"
and
"True" I can manually sum the values in AF resulting in the correct
value of
430. What am I doing wrong?

FYI if the array contains boolean values (TRUE or FALSE), then you don't need
"=TRUE" part. You can use this...

=SUMPRODUCT(--($H$28:$H$2000="Secure CE Lead"),--($T$28:$T$2000),AF28:AF2000)

"Dave Shultz" wrote:

Good catch. The result of column T is not text as I assumed. I changed
€œTrue€� to TRUE and it worked. Thanks for your help.

One of the other responders suggested {=SUM(IF($H$28:$H$2000="Secure CE
Lead",IF($T$28:$T$2000=TRUE,AF28:AF2000,0),0))} which also works just fine.
Is there any advantage either way?


"Peo Sjoblom" wrote:

A couple of things, you can try

=SUMPRODUCT(--($H$28:$H$2000="Secure CE
Lead"),--($T$28:$T$2000=TRUE),AF28:AF2000)

instead since "True" looks for the text whereas TRUE would be a result
of lets say =A20

If that does not work then you must have either hidden characters in
H28:H2000 like extra spaces or the values in AF28:AF2000 are text.

But I assume you have totaled them when using a filter so that doesn't
seem to be the problem


Regards,

Peo Sjoblom


Dave Shultz wrote:
I want to sum the values in AF28:AF2000 but only if the following two
conditions are met:

First, only if $H$28:$H$2000="Secure CE Lead" and second, only if
$T$28:$T$2000="True". I am using the following formula which returns 0.00 in
error.

=SUMPRODUCT(--($H$28:$H$2000="Secure CE
Lead"),--($T$28:$T$2000="True"),AF28:AF2000)

When I apply a filter to columns H and T and filter on "Secure CE Lead" and
€œTrue€� I can manually sum the values in AF resulting in the correct value of
430. What am I doing wrong?