Sum The Value of a Particular Range with Suffix Number by eliminating a Till Symbol Conten

[Rajeshkumar R]

Dear Experts,

Please find the attached File where I have daily production plan by individual FG Number.

Every Production Schedule is having the Prefix of “1=”, “2=”,”3=” Etc., Followed by Scheduled Quantity.

In Column AD2, I would want to sum the value Range of B2:AB2, Sum Formula has to consider the Value in Each Cell which is mentioned after “=” Symbol!

Few Expected Results are manually Updated in AD2, AD3 Cell

Looking forward your Input to Accomplish the same!

Thanks & Regards,
Rajeshkumar R

[samba_ravi]

try below formula
=SUMPRODUCT(--TRIM(MID(SUBSTITUTE(B2:AB2&"=0","=",REPT(" ",26)),26,26)))

[Rajeshkumar R]

Dear Ravi,

Thanks for your quick suggestion, The Formula works amazingly!

For My Better understanding of the Logic & Formula Could you please help me understand what exactly REPT(" ",26)),26,26))) this portion of formula executes and Why it is 26?

Thanks In Advange!
Rajeshkumar R

[samba_ravi]

I assume that the maximum length of the content in a cell would be 26,
rept(" ", 26) means it will produce 26 spaces
in the formula "=" will be substituted by " " (26 Spaces)
then the mid function will take the values 26 characters starting from 26th character.
for better understanding stay in the cell which you have formula
press Alt+m+v
then press on evaluate
here you can observe how the formula is working

[Rajeshkumar R]

Dear samba_ravi,

Noted, Thanks for your detailed Explanation!

Regards,
Rajeshkumar R