Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1554
  • Last Modified:

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
0
Flora
Asked:
Flora
2 Solutions
 
als315Commented:
Ca you explain idea of second criteria of your formula?
0
 
Patrick MatthewsCommented:
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.
0
 
Rgonzo1971Commented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
FloraAuthor Commented:
Patrick and Rgonzo

you guys are heros in EE.  

your formulas are amazing. frankly, i am impressed.
0
 
FloraAuthor Commented:
these answers are worth 50000.

saved me hours of manual work.
0
 
Patrick MatthewsCommented:
Glad to help :)

Also happy to see Rgonzo1971's SUMPRODUCT approach.  I tried something similar but couldn't get it to work, and so resorted to the explicit array formula (SUMPRODUCT can be made to work, as Rgonzo1971 ably demonstrated, as an implicit array function).
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now