How can I automate COUNTIF formulas across multiple Google sheets?

1
votes

Each release, a number of my reports help test for our engineering team, they are assigned cases in a google sheet and work through it.

Is there a way i can count the amount of times their name appears in a google sheet, from a separate sheet or app?

I know i could COUNTIF, but this would mean each week I'd have to go in, and write a bunch of countif formulas for the various people testing.

I was thinking there must be a way to write a script that searches for unique names in a column, then automatically writes the countif statements and prints them to a CSV or seperate sheet, but this is really the first thing I've tried to automate and am struggling to find the right info to get started, any direction or help would be greatly appreciated.

1
google-apps-scriptgoogle-sheetsautomation
Hello @Busyordead, you can use Google Apps Script to automate this task. Could you describe a bit more/present an example of what you are trying to accomplish? That would be of great help for us in providing a proper answer. Thanks! – carlesgg97
Sure, so essentially there are multiple columns in which an agent can put their name if they've tested the case. So for instance the workbook for the release will have 12+ different sheets for different parts of the site, and an agent may work on multiple sheets. I need to make an automation to run on all sheets and add each unique name's cases together so that i have a clear "cases this release" stat. Thank you for the help! – Busyordead

1 Answers

0
votes

You can use the =QUERY() Google Sheets function along with row concatenation. As an example, having a worksheet that has 3 sheets (named respectively Part1, Part2 and Part3) each of one having an issue column and an agent name column:

=QUERY({Part1!A2:B;Part2!A2:B;Part3!A2:B}, "SELECT Col2, COUNT(Col2) WHERE Col2 != '' GROUP BY Col2 LABEL Col2 'Agent', COUNT(Col2) 'Cases this release'")

Example

enter image description here

enter image description here

enter image description here

Result

enter image description here

You can see a working example of this feature in this public worksheet: https://docs.google.com/spreadsheets/d/1hrIMGsvSYOxHDoDCZNdYflE7PLh8Q_MOaeJBnr52tb8/edit?usp=sharing