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

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?