CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
eirever
eirever

Reputation: 143

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

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.

Upvotes: 1

Views: 2994

Answers (3)

Martí
Martí

Reputation: 2851

There are 3 things to notice:

  1. 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.
  2. 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.
  3. 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.

Upvotes: 1

doubleunary
doubleunary

Reputation: 18708

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 }.

Upvotes: 0

JPV
JPV

Reputation: 27242

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 ?

enter image description here

Upvotes: 1

Related Questions