Hello Experts,
How would you modify this formula so that the text and ="" [blanks] in the
range of B1:B8 are ignored; thus, not producing a #Value! error?

=SUMPRODUCT((A1:A8="a")*(B1:B8))

Thanks in advance,
Ricky

Try...

=SUMPRODUCT(--(A1:A8="a"),B1:B8)

Hope this helps!

In article ,
"Ricky Pang" wrote:

Hello Experts,
How would you modify this formula so that the text and ="" [blanks] in the
range of B1:B8 are ignored; thus, not producing a #Value! error?

=SUMPRODUCT((A1:A8="a")*(B1:B8))

Thanks in advance,
Ricky

Thanks Domenic,
That worked nicely.

Ricky

"Domenic" wrote in message
...
Try...

=SUMPRODUCT(--(A1:A8="a"),B1:B8)

Hope this helps!

In article ,
"Ricky Pang" wrote:

Hello Experts,
How would you modify this formula so that the text and ="" [blanks] in
the
range of B1:B8 are ignored; thus, not producing a #Value! error?

=SUMPRODUCT((A1:A8="a")*(B1:B8))

Thanks in advance,
Ricky

Ricky Pang wrote:
Hello Experts,
How would you modify this formula so that the text and ="" [blanks] in the
range of B1:B8 are ignored; thus, not producing a #Value! error?

=SUMPRODUCT((A1:A8="a")*(B1:B8))

Thanks in advance,
Ricky



If this is not a question about the behavior of SumProduct with a
contrieved example...

=SUMIF((A1:A8,"a",B1:B8)

will suffice.

Hello again,
How would you expand on this formula (which excludes text and "") so that
multiple data columns ranging from B1:H8 would be included in the result
once the criteria of ="a" has been established? Presently, only the data in
B1:B8 works. I need to expand the range.

=sumproduct(--(A1:A8="a"),B1:B8)

Thanks in advance,
Ricky


"Ricky Pang" wrote in message
...
Thanks Domenic,
That worked nicely.

Ricky

"Domenic" wrote in message
...
Try...

=SUMPRODUCT(--(A1:A8="a"),B1:B8)

Hope this helps!

In article ,
"Ricky Pang" wrote:

Hello Experts,
How would you modify this formula so that the text and ="" [blanks] in
the
range of B1:B8 are ignored; thus, not producing a #Value! error?

=SUMPRODUCT((A1:A8="a")*(B1:B8))

Thanks in advance,
Ricky

Try...

=SUM(IF(A1:A8="a",B1:H8))

....confirmed with CONROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"Ricky Pang" wrote:

Hello again,
How would you expand on this formula (which excludes text and "") so that
multiple data columns ranging from B1:H8 would be included in the result
once the criteria of ="a" has been established? Presently, only the data in
B1:B8 works. I need to expand the range.

=sumproduct(--(A1:A8="a"),B1:B8)

Thanks in advance,
Ricky

Hi Domenic,
Thank-you once again. It worked. This helps me a great deal.

Ricky

"Domenic" wrote in message
...
Try...

=SUM(IF(A1:A8="a",B1:H8))

...confirmed with CONROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"Ricky Pang" wrote:

Hello again,
How would you expand on this formula (which excludes text and "") so that
multiple data columns ranging from B1:H8 would be included in the result
once the criteria of ="a" has been established? Presently, only the data
in
B1:B8 works. I need to expand the range.

=sumproduct(--(A1:A8="a"),B1:B8)

Thanks in advance,
Ricky