技术问询：两类Excel求和公式需求——括号内数字求和与A类时长统计

Hey there! Let's tackle these two Excel formula questions one by one, with practical solutions you can drop right into your sheet:

1. Sum numbers inside parentheses (ignore text content) #

If your cells look like Apple(10), Banana(5), or any text string with numbers wrapped in parentheses, this formula will extract those numbers and sum them up reliably:

=SUMPRODUCT(--IFERROR(MID(A1:A10,SEARCH("(",A1:A10)+1,SEARCH(")",A1:A10)-SEARCH("(",A1:A10)-1),0))

Here's the breakdown:

• SEARCH("(", A1:A10) and SEARCH(")", A1:A10) pinpoint the positions of the opening and closing parentheses in each cell.
• MID(...) grabs the text between the parentheses (the number we care about).
• The double hyphen -- converts the extracted text into a numeric value (since MID returns text by default).
• IFERROR(..., 0) handles cells without parentheses gracefully, treating them as 0 instead of throwing an error.
• SUMPRODUCT adds up all the converted numbers across your target range (adjust A1:A10 to match your actual data).

2. Total hours for 'A' in row 1 (enter in cell B2) #

Assuming row 1 has entries like A:3, B:2, A:5 (where the prefix is the category and the suffix is hours), use this formula in B2 to get the total hours for 'A':

=SUMPRODUCT(--SUBSTITUTE(A1:Z1,"A:",""),--(LEFT(A1:Z1,1)="A"))

How it works:

• LEFT(A1:Z1,1)="A" checks which cells in row 1 start with 'A' (adjust the LEFT length if your category is longer, e.g., LEFT(...,2) for "AA").
• SUBSTITUTE(A1:Z1,"A:","") strips the "A:" prefix from matching cells, leaving just the hour number.
• Again, -- converts the text-based hour number to a numeric value.
• SUMPRODUCT multiplies the "is this an 'A' entry?" check (1 for yes, 0 for no) by the hour number, then sums all results to get your total.

If your row 1 format is different (like A 4 hours), feel free to adjust the logic—but this covers most common use cases!

内容的提问来源于stack exchange，提问作者Valentino