CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
S O
S O

Reputation: 41

Arrayformula to calculate sum for each entry in a list

I need to automatically calculate the respective score of list of entries in a list. Basically I need something in the lines of "If letters in column F exist in column A, then sum for each entry in column A and if letter not found in F then return 0". It should be an array type of formula.

I tried both query function, which didn't work the way I wrote it, and arrayformula, but it doesn't give me sum per row.

=query(b3:g,"select sum (G) when F contains B group by B")

=arrayformula(sumifs(G4:G,B4:B,F4:F))

I know you can use a combination with mmult, but I don't know how to combine that with matching the column values for the letters in the list.

The file is listed below and is structured as following. The score list in column A is fixed, and each month data in F-G column is posted, if the score is 0 for that month then the letter doesn't show up in the F column. I need an automatic way to calculate so I get the numbers in sheet 2.

https://docs.google.com/spreadsheets/d/1eyyqNL-LKw5F4kdDnbAPzN28jc4kWKMCJ-GAkn2yoXg/edit?usp=sharing

Upvotes: 1

Views: 854

Answers (1)

Harun24hr
Harun24hr

Reputation: 36870

MAP() or BYROW() will do the trick. Try below formula-

=MAP(B3:B17,LAMBDA(x,SUMIFS(G3:G,F3:F,x)))

For >300 use-

=MAP(B3:B17,LAMBDA(x,SUMIFS(G3:G,F3:F,x,G3:G,">300")))

To make your input reference dynamic can use B3:INDEX(B3:B,COUNTA(B3:B)) as array argument or MAP() function. Try-

=MAP(B3:INDEX(B3:B,COUNTA(B3:B)),LAMBDA(x,SUMIFS(G3:G,F3:F,x)))

enter image description here

Upvotes: 2

Related Questions