C
CuriousGeorge408
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:B54),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?
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:B54),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?