Sumproduct doesn't pick up text

[simran555]

Dear All,




Kindly assist in getting the total without changing the values to number. I have attached the excel sheet

Appreciate the help

[Pepe Le Mokko]

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[simran555]

I am hoping to get the amount of 800 without formatting the cells in the source 3 sheet to number.

[Pepe Le Mokko]

Use the double unary operator -- to take text into account like

=SUMPRODUCT(--(Source3!$B$4:$B$6='All sheet'!B2)*Source3!$C$4:$C$6)

You'll find explanations all over the Net

BTW no need to make SUMPRODUCT an array formula, it already is

[simran555]

SUMPRODUCT(--(Source3!$D$4:$D$300='All sheet'!C473)*(Source3!$AE$4:$AE$300))

Thank you for your response but when copied the formula to the actual worksheet it gives a #VALUE! error. Kindly advise

[Glenn Kennedy]

If it doesn't work, thensomething else s going on. Post a sheet showing the error. If we can't see it we can't diagnose it.

however, as I told you before, it only takes a second to select ALL the data and convert it to a number.

[simran555]

Hello Mr. Glenn,

Please see the attached sheet.

[Bo_Ry]

Please try
=SUMPRODUCT((Source3!$C$4:$C$262=B2)*TEXT(Source3!$D$4:$D$262,"0.00;;0;\0"))

but better change Source3!$D$4:$D$262 to number to use Faster non-array formula
=SUMIFS(Source3!$E$4:$E$262,Source3!$C$4:$C$262,B2)

[simran555]

This works perfectly !!! . I Hope there wont be any issues. Could you please this - 0.00;;0;\0")) in the equation.

[Bo_Ry]

Number format

POSITIVE;NEGATIVE; ZERO; TEXT
0.00;;0;\0

Change Positive number to 0.00 number with 2 decimal

Negative to blank, there is no negative number.
Zero to 0

Text becomes 0