How to add a COUNTIF within an ARRAYFORMULA using wildcards/partial match

Question

I am building a dashboard to track various things related to team member roles. This includes how many times they lead an event as well as when they support in various functions. I am interested in adding a count of the number of "support roles" for each team member. To accomplish this, I currently use a separate formula with a total COUNTIF that uses "*" wildcards around the name minus the COUNTIF for the times each person Leads (since this is not a support role and is counted in the QUERY function.

=COUNTIF($A$1:$E,"*"&G2&"*")-COUNTIF($A$1:$A,G2)

This means I have to fill the formula when more team members take the lead on events.

A model of this data structure is on the "array" sheet in the following link: https://docs.google.com/spreadsheets/d/1lyCK5eIEQYjGFOxh5T_4BxeuDS-1zvMLq80IYn0dc38/edit#gid=1056844943

K2:K5 have the manual COUNTIF formulas. G8 uses the array of formulas that creates the desired table format (i.e., a COUNTIF formula created within the array set in G8).

={QUERY(A1:E,"select A, count(A) where A is not null group by A label A 'Lead', count(A) 'Total Leads'",1),QUERY(A1:E,"select sum(B) where A is not null group by A label sum(B) 'Total #1'",1),QUERY(A1:E,"select sum(C) where A is not null group by A label sum(C) 'Total #2'",1),{"Total Support Roles";ARRAYFORMULA(COUNTIF((A2:E),QUERY(QUERY(A2:E,"select A, count(A) where A is not null group by A",0),"select Col1 where Col1 is not null",0)))}}

The last subarray includes the ARRAYFORMULA(COUNTIF structure; unfortunately, I cannot successfully use this formula for with partial matches/wild cards as I'm using nested QUERY formulas to dynamically update & match the array size (it also does not include the headers to keep the array size aligned).

=ARRAYFORMULA(COUNTIF((A2:E),QUERY(QUERY(A2:E,"select A, count(A) where A is not null group by A",0),"select Col1 where Col1 is not null",0)))

In my mind, there may be some combination of formulas potentially including SPLIT to create a solution; unfortunately, I have not found one and thus am seeking help. Many thanks for your consideration.

update: link changed to open directly to "array" tab

eirever
– eirever

2021-04-23 06:32:32 +00:00
Commented Apr 23, 2021 at 6:32 — eirever
– eirever, Commented Apr 23, 2021 at 6:32

JPV · Accepted Answer · 2021-04-23 06:52:39Z

1

Try this formula

=ArrayFormula(QUERY({A2:E, COUNTIF(B2:E, "*"&A2:A&"*")},"select Col1, count(Col1), sum(Col2), sum(Col3), Col6  where Col1 is not null group by Col1, Col6 label Col1 'Lead', count(Col1) 'Total Leads', sum(Col2) 'Total #1', sum(Col3) 'Total #2', Col6 'Total Support Roles'",0))

and see if that helps ?

answered Apr 23, 2021 at 6:52

JPV

27.4k4 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martí · Accepted Answer · 2021-04-23 11:31:52Z

1

There are 3 things to notice:

countif($A$1:$E,"*"&G2&"*")-countif($A$1:$A,G2) is actually equivalent of countif($B$1:$E,"*"&G2&"*"). Instead of adding the column A and then subtracting it, it doesn't get counted in the first place.
arrayformula(countif($B$1:$E,"*"&G2:G&"*")) does what you would expect: For each row calculates the count if changing the cell on row G each time.
You can use array syntax ({range1, range2}) to join the current range used for the query with this result. Worth noting that depending on your locale you may need to change the comma (,) with a backslash (\).

JPV already figured this out, so they made the formula. Here is the same formula but spread, so it's easier to understand:

=arrayformula(
  query(
    {A2:E, countif(B2:E, "*"&A2:A&"*")},
    "SELECT
        Col1,
        count(Col1),
        sum(Col2),
        sum(Col3),
        Col6
      WHERE
        Col1 is not null
      GROUP BY
        Col1,
        Col6
      LABEL
        Col1 'Lead',
        count(Col1) 'Total Leads',
        sum(Col2) 'Total #1',
        sum(Col3) 'Total #2',
        Col6 'Total Support Roles'
    ",
    0
  )
)

Col1 to Col5 are the columns A to E. Col6 is the countif result.

Note that you can paste it formatted like this into Google Sheets and will work.

answered Apr 23, 2021 at 11:31

community wiki

Martí

1 Comment

eirever Over a year ago

many thanks for the response. to your first note, yes, this makes perfect sense; it took a while to remember why i did it like this. i remembered that i was using a named range and didn't want to change it. i'll create a new named range that still updates dynamically to your second note, thanks for clarifying the way to set a COUNTIF criterion with wildcards for an ARRAYFORMULA; very helpful for understanding to your third not, similarly, the formatting shift was very helpful for understanding what was actually happening in @JPV response. thanks again!

doubleunary · Accepted Answer · 2021-04-23 06:54:59Z

0

Try this to get the Total Support Roles row-by-row in K2:

=arrayformula( countif(D2:E, "*" & G2:G5 & "*") )

With some re-arranging, the formula could be made part of the longer array formula you have in G8, but I would recommend that you place each sub-formula in its own cell instead. You could eventually have with trouble with mismatched array heights if you keep all the formulas wrapped in the same { array expression }.

answered Apr 23, 2021 at 6:54

doubleunary

21.9k13 gold badges38 silver badges85 bronze badges

Collectives™ on Stack Overflow

How to add a COUNTIF within an ARRAYFORMULA using wildcards/partial match

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related