CODEWITHSUNDEEP
google-sheets
ajor
ajor

Reputation: 1634

Utilising COUNTIF in array formula in Google Sheets

I have some data that I want to summarise at a grouped level, where the possible values for each group are given as a pipe-delimited string. I then perform a sumifs by summing over multiple arrays, one of which includes a COUNTIF() with a SPLIT() in order to establish if the row features in this set of values.

The formula works fine but I would ideally like it to function as an array formula so that if the number of groups changes, the number of rows the formula is applied to will also change.

See sample sheet here. Raw data is in the tab "data", the groupings data is in the tab "Groupings" and it is the formula in column C on the "Summary" tab that I want to make work as an array formula.

Upvotes: 1

Views: 174

Answers (1)

MattKing
MattKing

Reputation: 7773

I think the easiest way to do this is to mark the Group on the Data sheet and then use a traditional query to add up the groups. This vlookup should do it in cell D1:

=ARRAYFORMULA({"Group";IF(A2:A="",,VLOOKUP("*|"&A2:A&"|*",{"|"&Groupings!D:D&"|",Groupings!C:C},2,0))})

Upvotes: 1

Related Questions