Help with SUMPRODUCT Function

Hello -
I'm in need of some help. I need to count the number of occurences (rows)
in a Worksheet, based on two criteria (column entries) in that row.

I've been trying to use the 'SUMPRODUCT' function, but keep winding up with
a value of 0 (although I know the answer should be 0, when I count portions
manually). The formula I've been trying to use is below:

=SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= "TRUE"))

Where "Data!" is the tab the information is on (the report/detail I'm
attempting to compile in on another tab), column K identifies the state (in
this case AL), and column AB is a flag for whether or not a product has been
included (the only values are True or False).

I've tried everything I can think of, but still can't seem to get this to
work. Any help would be most appreciated !

Thanks for your help,
- Matt

Hi,

Did you try removing the quotes from around TRUE?

=SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= TRUE))

Mike

"MattyP" wrote:

Hello -
I'm in need of some help. I need to count the number of occurences (rows)
in a Worksheet, based on two criteria (column entries) in that row.

I've been trying to use the 'SUMPRODUCT' function, but keep winding up with
a value of 0 (although I know the answer should be 0, when I count portions
manually). The formula I've been trying to use is below:

=SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= "TRUE"))

Where "Data!" is the tab the information is on (the report/detail I'm
attempting to compile in on another tab), column K identifies the state (in
this case AL), and column AB is a flag for whether or not a product has been
included (the only values are True or False).

I've tried everything I can think of, but still can't seem to get this to
work. Any help would be most appreciated !

Thanks for your help,
- Matt

I'm guessing that col_AB contains boolean values.
Try this:
=SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= TRUE))

Does that help?

Regards,

Ron Coderre
Microsoft MVP (Excel)

"MattyP" wrote in message
...
Hello -
I'm in need of some help. I need to count the number of occurences (rows)
in a Worksheet, based on two criteria (column entries) in that row.

I've been trying to use the 'SUMPRODUCT' function, but keep winding up
with
a value of 0 (although I know the answer should be 0, when I count
portions
manually). The formula I've been trying to use is below:

=SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= "TRUE"))

Where "Data!" is the tab the information is on (the report/detail I'm
attempting to compile in on another tab), column K identifies the state
(in
this case AL), and column AB is a flag for whether or not a product has
been
included (the only values are True or False).

I've tried everything I can think of, but still can't seem to get this to
work. Any help would be most appreciated !

Thanks for your help,
- Matt

Hi
try this

=SUMPRODUCT((Data!K2:K10="AL")*(Data!AB2:AB10=TRUE ))

remove "" from TRUE
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

I am an ordinary user trying to assist another

Thank You

cheers, francis



"MattyP" wrote:

Hello -
I'm in need of some help. I need to count the number of occurences (rows)
in a Worksheet, based on two criteria (column entries) in that row.

I've been trying to use the 'SUMPRODUCT' function, but keep winding up with
a value of 0 (although I know the answer should be 0, when I count portions
manually). The formula I've been trying to use is below:

=SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= "TRUE"))

Where "Data!" is the tab the information is on (the report/detail I'm
attempting to compile in on another tab), column K identifies the state (in
this case AL), and column AB is a flag for whether or not a product has been
included (the only values are True or False).

I've tried everything I can think of, but still can't seem to get this to
work. Any help would be most appreciated !

Thanks for your help,
- Matt

Thank you both......that did it ! (For some reason, I think assumed the
"comparison quotes" were needed since the value wasn't an actual #.......I
didn't even think to take boolean logic/values into account !).

Thanks again to you both for your help (and quick replies) --- I appreciate
it !
- Matt

"Ron Coderre" wrote:

I'm guessing that col_AB contains boolean values.
Try this:
=SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= TRUE))

Does that help?

Regards,

Ron Coderre
Microsoft MVP (Excel)

"MattyP" wrote in message
...
Hello -
I'm in need of some help. I need to count the number of occurences (rows)
in a Worksheet, based on two criteria (column entries) in that row.

I've been trying to use the 'SUMPRODUCT' function, but keep winding up
with
a value of 0 (although I know the answer should be 0, when I count
portions
manually). The formula I've been trying to use is below:

=SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= "TRUE"))

Where "Data!" is the tab the information is on (the report/detail I'm
attempting to compile in on another tab), column K identifies the state
(in
this case AL), and column AB is a flag for whether or not a product has
been
included (the only values are True or False).

I've tried everything I can think of, but still can't seem to get this to
work. Any help would be most appreciated !

Thanks for your help,
- Matt

Hi,

Actually you can simplify your formula in the current case to read:

=SUMPRODUCT((Data!K1:K3000="AL")*Data!AB1:AB3000)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"MattyP" wrote:

Hello -
I'm in need of some help. I need to count the number of occurences (rows)
in a Worksheet, based on two criteria (column entries) in that row.

I've been trying to use the 'SUMPRODUCT' function, but keep winding up with
a value of 0 (although I know the answer should be 0, when I count portions
manually). The formula I've been trying to use is below:

=SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= "TRUE"))

Where "Data!" is the tab the information is on (the report/detail I'm
attempting to compile in on another tab), column K identifies the state (in
this case AL), and column AB is a flag for whether or not a product has been
included (the only values are True or False).

I've tried everything I can think of, but still can't seem to get this to
work. Any help would be most appreciated !

Thanks for your help,
- Matt

As long as column AB contains only the logical values TRUE or FALSE (or
empty cells):

=SUMPRODUCT(--(Data!K1:K3000="AL"),--Data!AB1:AB3000)

If you're using Excel 2007:

=COUNTIFS(Data!K1:K3000,"AL",Data!AB1:AB3000,TRUE)

--
Biff
Microsoft Excel MVP


"MattyP" wrote in message
...
Hello -
I'm in need of some help. I need to count the number of occurences (rows)
in a Worksheet, based on two criteria (column entries) in that row.

I've been trying to use the 'SUMPRODUCT' function, but keep winding up
with
a value of 0 (although I know the answer should be 0, when I count
portions
manually). The formula I've been trying to use is below:

=SUMPRODUCT((Data!K1:K3000="AL")*(Data!AB1:AB3000= "TRUE"))

Where "Data!" is the tab the information is on (the report/detail I'm
attempting to compile in on another tab), column K identifies the state
(in
this case AL), and column AB is a flag for whether or not a product has
been
included (the only values are True or False).

I've tried everything I can think of, but still can't seem to get this to
work. Any help would be most appreciated !

Thanks for your help,
- Matt