How to make SUMPRODUCT work over range that includes text?

I have a column of text labels, with subtotals denoted by "TOTAL", and a
corresponding column of amounts. However, the latter includes some B-cells
with text, but none where the corresponding A-cell is "TOTAL".

I would like to write:

=SUMPRODUCT(EXACT(A1:A54,"TOTAL")*B1:B54)

to sum the subtotals. But the text in some B-cells causes a #VALUE error.
I tried:

=SUMPRODUCT(EXACT(A1:A54,"TOTAL")*N(B1:B54))

to no avail; the result is zero. I also tried:

=SUMPRODUCT(EXACT(A1:A54,"TOTAL")*IF(ISNUMBER(B1:B 54),B1:B54))

That, too, results in zero. So far, the only thing that works is the array
formula:

=SUM(EXACT(A1:A54,"TOTAL")*IF(ISNUMBER(B1:B54),B1: B54))

But I was hoping to avoid an array formula by using SUMPRODUCT.

Is there a non-array formula solution?

Hi try this

=SUMPRODUCT(--(A1:A54="TOTAL"),--(B1:B54"*"),B1:B54)
--
Hope this is helpful

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

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"CuriousGeorge408" wrote:

I have a column of text labels, with subtotals denoted by "TOTAL", and a
corresponding column of amounts. However, the latter includes some B-cells
with text, but none where the corresponding A-cell is "TOTAL".

I would like to write:

=SUMPRODUCT(EXACT(A1:A54,"TOTAL")*B1:B54)

to sum the subtotals. But the text in some B-cells causes a #VALUE error.
I tried:

=SUMPRODUCT(EXACT(A1:A54,"TOTAL")*N(B1:B54))

to no avail; the result is zero. I also tried:

=SUMPRODUCT(EXACT(A1:A54,"TOTAL")*IF(ISNUMBER(B1:B 54),B1:B54))

That, too, results in zero. So far, the only thing that works is the array
formula:

=SUM(EXACT(A1:A54,"TOTAL")*IF(ISNUMBER(B1:B54),B1: B54))

But I was hoping to avoid an array formula by using SUMPRODUCT.

Is there a non-array formula solution?

Hi I have misread your post

here is a shorter version

=SUMPRODUCT(--(A1:A54="TOTAL"),B1:B54) or
you can use a cell reference for "Total", assume you type Total in cell A55

the formula will change to
=SUMPRODUCT(--(A1:A54=A55),B1:B54)
--
Hope this is helpful

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

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"CuriousGeorge408" wrote:

I have a column of text labels, with subtotals denoted by "TOTAL", and a
corresponding column of amounts. However, the latter includes some B-cells
with text, but none where the corresponding A-cell is "TOTAL".

I would like to write:

=SUMPRODUCT(EXACT(A1:A54,"TOTAL")*B1:B54)

to sum the subtotals. But the text in some B-cells causes a #VALUE error.
I tried:

=SUMPRODUCT(EXACT(A1:A54,"TOTAL")*N(B1:B54))

to no avail; the result is zero. I also tried:

=SUMPRODUCT(EXACT(A1:A54,"TOTAL")*IF(ISNUMBER(B1:B 54),B1:B54))

That, too, results in zero. So far, the only thing that works is the array
formula:

=SUM(EXACT(A1:A54,"TOTAL")*IF(ISNUMBER(B1:B54),B1: B54))

But I was hoping to avoid an array formula by using SUMPRODUCT.

Is there a non-array formula solution?

In article ,
"CuriousGeorge408" wrote:

I have a column of text labels, with subtotals denoted by "TOTAL", and a
corresponding column of amounts. However, the latter includes some B-cells
with text, but none where the corresponding A-cell is "TOTAL".

I would like to write:

=SUMPRODUCT(EXACT(A1:A54,"TOTAL")*B1:B54)

to sum the subtotals. But the text in some B-cells causes a #VALUE error.
I tried:

=SUMPRODUCT(EXACT(A1:A54,"TOTAL")*N(B1:B54))

to no avail; the result is zero. I also tried:

=SUMPRODUCT(EXACT(A1:A54,"TOTAL")*IF(ISNUMBER(B1:B 54),B1:B54))

That, too, results in zero. So far, the only thing that works is the array
formula:

=SUM(EXACT(A1:A54,"TOTAL")*IF(ISNUMBER(B1:B54),B1: B54))

But I was hoping to avoid an array formula by using SUMPRODUCT.

Is there a non-array formula solution?

Try...

=SUMPRODUCT(--(EXACT(A1:A54,"TOTAL")),B1:B54)

However, if case-sensitivity is not an issue, the following should
suffice...

=SUMIF(A1:A54,"Total",B1:B54)

--
Domenic
http://www.xl-central.com

"Domenic" wrote:
Try...
=SUMPRODUCT(--(EXACT(A1:A54,"TOTAL")),B1:B54)

Well, that was obvious. (No need for the redundant parentheses around the
EXACT function, though.) I thought I had tried that, but apparently not.
Klunk!

Thanks.


----- original message -----

"Domenic" wrote in message
...
In article ,
"CuriousGeorge408" wrote:

I have a column of text labels, with subtotals denoted by "TOTAL", and a
corresponding column of amounts. However, the latter includes some
B-cells
with text, but none where the corresponding A-cell is "TOTAL".

I would like to write:

=SUMPRODUCT(EXACT(A1:A54,"TOTAL")*B1:B54)

to sum the subtotals. But the text in some B-cells causes a #VALUE
error.
I tried:

=SUMPRODUCT(EXACT(A1:A54,"TOTAL")*N(B1:B54))

to no avail; the result is zero. I also tried:

=SUMPRODUCT(EXACT(A1:A54,"TOTAL")*IF(ISNUMBER(B1:B 54),B1:B54))

That, too, results in zero. So far, the only thing that works is the
array
formula:

=SUM(EXACT(A1:A54,"TOTAL")*IF(ISNUMBER(B1:B54),B1: B54))

But I was hoping to avoid an array formula by using SUMPRODUCT.

Is there a non-array formula solution?

Try...

=SUMPRODUCT(--(EXACT(A1:A54,"TOTAL")),B1:B54)

However, if case-sensitivity is not an issue, the following should
suffice...

=SUMIF(A1:A54,"Total",B1:B54)

--
Domenic
http://www.xl-central.com