I have a data sheet that was given to me where all numbers are stored as text. I need to sum all instances, of one value, but there are different variations of the matching criteria. So, for example, if I wanted to sum all results for 400044, I would use this formula:
However, because all of Column B is formatted as text, the result is 0.
I can get the correct result by physically changing each cell to a number, but due to the way the spreadsheet is set up, I'd have to do that for each and every cell. So if there is some variation of my formula that would ignore the "text" format, and just sum up anyway, that would be awesome.
Column A |
Column B |
Column C |
Column D |
Name |
Result |
Total Of: |
|
330864 : 1 . 6 |
1148.11 |
400044 |
(Formula here in D2) |
330864 : 1 . 6 |
798.71 |
|
|
330864 : 1 . 6 |
1166.05 |
|
|
330864 : 1 . 6 |
1821.54 |
|
|
330864 : 1 . 6 |
579.73 |
|
|
330864 : 1 . 6 |
220.06 |
|
|
330864 : 2 . 7 |
1189.29 |
|
|
330864 : 2 . 7 |
850.62 |
|
|
330864 : 2 . 7 |
1129.55 |
|
|
330864 : 2 . 7 |
1863.24 |
|
|
330864 : 2 . 7 |
569.68 |
|
|
330864 : 2 . 7 |
253.54 |
|
|
400044 : 1 . 8 |
1171.75 |
|
|
400044 : 1 . 8 |
849.91 |
|
|
400044 : 1 . 8 |
1145.93 |
|
|
400044 : 1 . 8 |
1940.84 |
|
|
400044 : 1 . 8 |
584.29 |
|
|
400044 : 1 . 8 |
172.44 |
|
|
400044 : 2 . 9 |
1186.25 |
|
|
400044 : 2 . 9 |
857.92 |
|
|
400044 : 2 . 9 |
1155.86 |
|
|
400044 : 2 . 9 |
1943.53 |
|
|
400044 : 2 . 9 |
587.68 |
|
|
400044 : 2 . 9 |
175.88 |
|
|
Bookmarks