Combining TEXT() with SUMPRODUCT()

[cotoews]

Hi all, I have a range of numbers I'm summing via sumproduct such that B1:B10 is the values to sum, A1:10 is my range of 1's and 0's. I'm also trying to wrap a TEXT() function around the whole thing and dynamically change the formatting of the result, based on some logic in A11. A11 could equal #,##0 or $,##0.00 - depending on user selection (to round or not).

The formula is TEXT(SUMPRODUCT($A1:$A10,B1:B10),$A$11), but the result is always 0. If I pull the TEXT() piece out, the sumproduct works as expected, and if I wrap the TEXT() formula around one of the values in my B1:B10 range, the number formatting works as expected. It seems that Excel doesn't like something about combining TEXT and SUMPRODUCT. Similarly, I tried simplifying the SUMPRODUCT to just SUM (and pulled out the A1:A10 validation), and it still resulted in 0.

Any luck getting text and sumproduct to play nice together?

[Sam Capricci]

sumproduct will work with text but it depends on how you are using it...
for example this... =SUMPRODUCT(--(TEXT($A$1926:$A$1944,"yyyy")=C1926)) worked for me where I had dates (such as 7/12/2015) in col A and also a year (formatted as text) in cell C1926.

[jason.b75]

If sumproduct works without the text function then why not just use that and apply conditional formatting to the result?

[cotoews]

jason.b- I'm using third party software that opens up a managed instance of Excel. The TPS doesn't play nicely with CF.

All- Sambokid alluded to the answer. I was also wrapping the text() around the values in my B1:B10, so the sum or sumproduct functions didn't know how to perform math on (what I had turned into) non-numeric cells. Thanks for the replies, all.