SUMIFS number stored as text

Hi,

please see attached worksheet.   I have SUMIFS formula in Column D and it does not work because Column B data are numbers stored as text.  I want help with formula to modify the formula, so that without converting the B column to number, my formula would work.
EE.xlsx
LVL 6
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Ca you explain idea of second criteria of your formula?
Commented:
The following array formula works:

{=SUM(IF((\$A\$2:\$A\$19=A2)*(VALUE(\$B\$2:\$B\$19)<=VALUE(B2)),\$C\$2:\$C\$19,0))}

Enter it WITHOUT the curly braces, and the use Ctrl+Shift+Enter instead of Enter to finish it.  Excel will then display the formula within braces to indicate that it is an array formula.

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Hi,

pls try ( as array formula Ctrl-Shift-Enter)

=SUMPRODUCT(\$C\$2:\$C\$19,--(\$A\$2:\$A\$19=A2),--(VALUE(\$B\$2:\$B\$19)<=VALUE(B2)))

Regards
Author Commented:
Patrick and Rgonzo

you guys are heros in EE.

your formulas are amazing. frankly, i am impressed.
Author Commented: