I have the following Google Sheets table and want to calculate the total sums of various medications prescribed:

1 Type Amount (in g) Daily dosage (in g)
2 A 60 2
3 B 25 mg/capsule 30 pieces, 0-0-1 for 30 days
4 A 10 0.5

This is very easy for medication type A, as I can just look in column B.

My issue is that the entries associated with medication type B don't only consist of digits, so I need to apply two REGEXTRACTs.


"(\d+) pieces"

That is all fine and would work.

But how do I get around the issue that the sum_range in SUMIFS needs to be digits?

Because something like this would not work afaik:


I cannot change the way this data is entered and also don't want to create additional columns if possible.

  • Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. May 7 at 16:36


From Google Sheets List Function

Type Name Syntax Description
Text VALUE VALUE(text) Converts a string in any of the date, time or number formats that Google Sheets understands into a number. Learn more

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.