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

x
Solved

# SUMIFS number stored as text

Posted on 2014-11-20
Medium Priority
1,469 Views
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
Question by:Flora

LVL 40

Expert Comment

ID: 40456665
Ca you explain idea of second criteria of your formula?
0

LVL 93

Accepted Solution

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

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

LVL 6

Author Comment

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

ID: 40456919

saved me hours of manual work.
0

LVL 93

Expert Comment

ID: 40457838

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

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…
###### Suggested Courses
Course of the Month11 days, 17 hours left to enroll