CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Ilgar Safarli
Ilgar Safarli

Reputation: 81

I need to make special formula to sum only specific cells containing the row with text in Google Sheets

I have more than 1000 columns and rows. I need to calculate the total numbers only from specific names (in this case the total of numbers which are highlighted on the pictures ) enter image description here

Upvotes: 0

Views: 46

Answers (3)

player0
player0

Reputation: 1

something more advanced.... you can use this formula in all of your 3 cases:

=SUM(FILTER(C$2:E$5*1, REGEXMATCH(LOWER(A$2:A$5), TEXTJOIN("|", 1, IFERROR(
 REGEXEXTRACT(LOWER(SPLIT(A8, " ")), TEXTJOIN("|", 1, LOWER(A$2:A$5))))))))

enter image description here

spreadsheet demo

Upvotes: 1

idfurw
idfurw

Reputation: 5862

In case you have many criteria or the number of criteria varies, you may consider to use QUERY

Assuming the criteria are on B1002:1002 (i.e. B1002: Alice, C1002: Bob, D1002: Ted) and the table is A1:AA1000

=ArrayFormula(SUM(QUERY({A1:AA1000},"select sum(Col"&TEXTJOIN(")+sum(Col",TRUE,IF(B1:1="",,COLUMN(B1:1)))&") where Col1='"&TEXTJOIN("' or Col1='",TRUE,B1002:1002)&"' group by Col1",1)))

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36945

There are multiple way to do this. Try FILTER() then SUM() formula.

For Alice =SUM(FILTER(C2:E5,A2:A5="Alice"))
For alice+Bob =SUM(FILTER(C2:E5,A2:A5="Alice"))+SUM(FILTER(C2:E5,A2:A5="Bob"))
For bob =SUM(FILTER(C2:E5,A2:A5="Bob"))

Another way is to use SUMPRODUCT() like

=SUMPRODUCT((C2:E5)*(A2:A5="Alice"))
=SUMPRODUCT((C2:E5)*((A2:A5="Alice")+(A2:A5="Bob")))
=SUMPRODUCT((C2:E5)*(A2:A5="Bob"))

enter image description here

Upvotes: 2

Related Questions