Using SUMPRODUCT, INDIRECT and MID to add numbers in brackets

I'm looking to take the numbers from one cell (X15), from a named range called "Weekdays" (which includes the sheets Monday, Tuesday, Wednesday, Thursday, Friday), extract the number within the square brackets (ex. <2>) and sum the numbers.

I already have a formula that does this for numbers without the square brackets around them, so I probably just need a slight adjustment to get it to do what I want. Here's the formula I'm using for that:

=SUMPRODUCT(SUM(INDIRECT("'"&Weekdays&"'!"&"X13")))

I tried experimenting with SUM and MID to figure it out myself, putting <2> in K16 and <4> in L16, and using the formula {=SUM(MID($K$16:$L$16,2,1)*1)} worked perfectly. Just getting Excel to use my named range with INDIRECT is giving me trouble.

Apologies if that's overly wordy.. I appreciate any help on this issue.

1 answer

  • answered 2017-01-11 14:18 Glitch_Doctor

    You are using a 3D Named reference, though you can formulate a 3D reference without using the named range providing the sheets are in a block next to each other meaning that there is no need for INDIRECT:

    =SUM(Monday:Friday!X13)

    However, as MID() cannot be used with 3D references (List of possible formulas here) we hit a bit of a problem. Does the solution have to be formulated or could a User Defined Function in VBA be used?

    Perhaps you have scope to include a helper column next to X13 on each page with =SUBSTITUTE(SUBSTITUTE(X13,"<",""),">","") then sum those instead?