Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SUMIFS number stored as text

Posted on 2014-11-20
6
Medium Priority
?
1,469 Views
Last Modified: 2014-11-21
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
Comment
Question by:Flora
6 Comments
 
LVL 40

Expert Comment

by:als315
ID: 40456665
Ca you explain idea of second criteria of your formula?
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 40456735
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
 
LVL 53

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 1000 total points
ID: 40456741
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.

 
LVL 6

Author Comment

by:Flora
ID: 40456913
Patrick and Rgonzo

you guys are heros in EE.  

your formulas are amazing. frankly, i am impressed.
0
 
LVL 6

Author Closing Comment

by:Flora
ID: 40456919
these answers are worth 50000.

saved me hours of manual work.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 40457838
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question