Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make SUMPRODUCT work over range that includes text?
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make SUMPRODUCT work over range that includes text?
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make SUMPRODUCT work over range that includes text?
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make SUMPRODUCT work over range that includes text?
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto-filter includes too much range | Excel Discussion (Misc queries) | |||
Find most recent value in data range that includes SUM formulas | Excel Worksheet Functions | |||
How do I make this work? =IF(C11=TEXT,D11,D11-C11)*24 | Excel Worksheet Functions | |||
=max(range includes #N/As) | Excel Worksheet Functions | |||
How do I sum a range which includes the "#N/A" VLOOKUP return valu | Excel Worksheet Functions |