how do I add the total of Q in excel

Question

so this is a cell in microsoft {"Q0":"2","Q1":"1","Q2":"2","Q3":"1","Q4":"2","Q5":"3","Q6":"2","Q7":"1","Q8":"2"} How do I make a function that adds up all the number. (in this case 2+1+2+1+2+3+2+1+2=16)

That looks like JSON. If you Google for JSON Excel you end up with lots of answers. This one is an example: https://codingislove.com/excel-json/ But there are lots of others. — Jerry Jeremiah, Sep 26 '18 at 02:55
Possible duplicate: https://stackoverflow.com/questions/6627652/parsing-json-in-excel-vba — Jerry Jeremiah, Sep 26 '18 at 02:56
Here is another one I found with Google: https://github.com/omegastripes/VBA-JSON-parser And some SO questions about how to use it: https://stackoverflow.com/search?q=user%3A2165759+is%3Aanswer+json.bas — Jerry Jeremiah, Sep 26 '18 at 03:00

score 1 · Answer 1 · answered Sep 26 '18 at 04:41

There are lots of Google hits for VBA functions that parse generic JSON, but if you want worksheet functions that work for your particular format try this:

There are two ways to do this. You can try to parse the json yourself but the array formulas become unwieldy (see the second half of the answer). Or you can convert the JSON to XML and then use the built in XML filtering function.

The "convert JSON to XML" way

To change JSON to XML you need to:

substitute {" with <a><b><c>
substitute ":" with </c><d>
substitute "," with </d></b><b><c>
substitute "} with </d></b></a>

That will change something like:

{"Q0":"0","Q1":"1"}

into:

<a><b><c>Q0</c><d>0</d></b><b><c>Q1</c><d>1</d></b></a>

You could, for example, put into B8 this formula for doing the JSON to XML conversion of a value in B2:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,
"{""",      "<a><b><c>"),
""":""",    "</c><d>"),
""",""",    "</d></b><b><c>"),
"""}",      "</d></b></a>")

After that you can use this XML Filter function to get the sum of the values of an XML string that is in B8:

=SUM(FILTERXML(B8,"//d"))

It is an array formula so after you put it in a cell press Control-Shift-Enter instead of just pressing Enter.

So the entire array formula including the substitutions is:

=SUM(FILTERXML(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,
"{""",      "<a><b><c>"),
""":""",    "</c><d>"),
""",""",    "</d></b><b><c>"),
"""}",      "</d></b></a>"),
"//d"))

The "parsing it yourself" way

If you really do want to parse the JSON yourself then I don't know of a way to get the sum with one formula in one cell, but if you display the data in cells and then add it up it will work.

If The JSON is in B2 you can enter this array formula in A5 to get the first key:

=SUBSTITUTE(LEFT(MID(","&MID($B2,2,LEN($B2)-2)&",",
    SMALL(IF(ISERROR(SEARCH(MID(","&MID($B2,2,LEN($B2)-2)&",", ROW($1:$99), 1), ",")), "", ROW($1:$99)+1), COLUMN(A1)),
    SMALL(IF(ISERROR(SEARCH(MID(","&MID($B2,2,LEN($B2)-2)&",", ROW($1:$99), 1), ",")), "", ROW($1:$99)), COLUMN(A1)+1)-
    SMALL(IF(ISERROR(SEARCH(MID(","&MID($B2,2,LEN($B2)-2)&",", ROW($1:$99), 1), ",")), "", ROW($1:$99)), COLUMN(A1))-1),
    SEARCH(":",MID(","&MID($B2,2,LEN($B2)-2)&",",
        SMALL(IF(ISERROR(SEARCH(MID(","&MID($B2,2,LEN($B2)-2)&",", ROW($1:$99), 1), ",")), "", ROW($1:$99)+1), COLUMN(A1)),
        SMALL(IF(ISERROR(SEARCH(MID(","&MID($B2,2,LEN($B2)-2)&",", ROW($1:$99), 1), ",")), "", ROW($1:$99)), COLUMN(A1)+1)-
        SMALL(IF(ISERROR(SEARCH(MID(","&MID($B2,2,LEN($B2)-2)&",", ROW($1:$99), 1), ",")), "", ROW($1:$99)), COLUMN(A1))-1),
    1)-1
),"""","")

And this array formula in A6 to get the first value:

=--SUBSTITUTE(RIGHT(MID(","&MID($B2,2,LEN($B2)-2)&",",
    SMALL(IF(ISERROR(SEARCH(MID(","&MID($B2,2,LEN($B2)-2)&",", ROW($1:$99), 1), ",")), "", ROW($1:$99)+1), COLUMN(A1)),
    SMALL(IF(ISERROR(SEARCH(MID(","&MID($B2,2,LEN($B2)-2)&",", ROW($1:$99), 1), ",")), "", ROW($1:$99)), COLUMN(A1)+1)-
    SMALL(IF(ISERROR(SEARCH(MID(","&MID($B2,2,LEN($B2)-2)&",", ROW($1:$99), 1), ",")), "", ROW($1:$99)), COLUMN(A1))-1),
    LEN(MID(","&MID($B2,2,LEN($B2)-2)&",",
        SMALL(IF(ISERROR(SEARCH(MID(","&MID($B2,2,LEN($B2)-2)&",", ROW($1:$99), 1), ",")), "", ROW($1:$99)+1), COLUMN(A1)),
        SMALL(IF(ISERROR(SEARCH(MID(","&MID($B2,2,LEN($B2)-2)&",", ROW($1:$99), 1), ",")), "", ROW($1:$99)), COLUMN(A1)+1)-
        SMALL(IF(ISERROR(SEARCH(MID(","&MID($B2,2,LEN($B2)-2)&",", ROW($1:$99), 1), ",")), "", ROW($1:$99)), COLUMN(A1))-1))-
    SEARCH(":",MID(","&MID($B2,2,LEN($B2)-2)&",",
        SMALL(IF(ISERROR(SEARCH(MID(","&MID($B2,2,LEN($B2)-2)&",", ROW($1:$99), 1), ",")), "", ROW($1:$99)+1), COLUMN(A1)),
        SMALL(IF(ISERROR(SEARCH(MID(","&MID($B2,2,LEN($B2)-2)&",", ROW($1:$99), 1), ",")), "", ROW($1:$99)), COLUMN(A1)+1)-
        SMALL(IF(ISERROR(SEARCH(MID(","&MID($B2,2,LEN($B2)-2)&",", ROW($1:$99), 1), ",")), "", ROW($1:$99)), COLUMN(A1))-1),
    1)
),"""","")

And then you can copy A5 into B5:I5 and A6 into B6:I6 and finally just sum up A6:I6.

Just remember, they are array formulae so after you put it in a cell press Control-Shift-Enter instead of just pressing Enter.

I can't explain the formulas any better than the web page where I got them: https://www.get-digital-help.com/2011/03/23/text-to-columns-split-words-in-a-cell-excel-array-formula/ I really recommend you read their explanation of their formulas.

The changes I made to the formulas on that page consisted of:

removing the braces from the start and end: MID($B2,2,LEN($B2)-2) instead of $B2
splitting by commas instead of spaces: ","&MID($B2,2,LEN($B2)-2)&"," instead of " "&$B2&" "
replacing the double quotes with nothing: SUBSTITUTE(...,"""","")
making separate formulas that grab the key part with LEFT() and the value part with MID()
Adding the -- conversion from string to number for the results of the value formula

I have uploaded a sample spreadsheet to https://ufile.io/32eq8 and it won't expire for a couple weeks. — Jerry Jeremiah, Sep 26 '18 at 04:42

how do I add the total of Q in excel

1 Answers1

The "convert JSON to XML" way

The "parsing it yourself" way