Google sheets - Countifs across multiple different sheets but the same cells
I want to try to create a formula or a script that performs a countif across multiple different sheets (Sheets listed in A2:A1000). I've tried a couple of different things but both seem to not be working as I want them to be.
Formula method :
=ArrayFormula(SUM(IFERROR(COUNTIFS(INDIRECT("'"&A2:A1000&"'!B2:H2"), D1))))
This method seems to countifs on A2 but stops there, It doesn't count any of the data from any of the other sheets listed in the A2:A1000 range. Is there any work around for this cause the formula method would be preferred.
Script method :
Now the script method works and counts all the data I need across all the sheets... Perfect. Except if the data in any of the tabs changes, I have to delete the countOccurrencesAcrossSheets(B2:H2, D1) formula and reinput it. This wouldn't be an issue if it was just one cell I wanted to gather data with but I need about 1000 cells that count across multiple different sheets.
function countOccurrencesAcrossSheets(range, value) {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var count = 0;
for (var i = 0; i < sheets.length; i++) {
var sheet = sheets[i];
var data = sheet.getRange(range).getValues();
for (var row = 0; row < data.length; row++) {
for (var col = 0; col < data[row].length; col++) {
if (data[row][col] === value) {
count++;
}
}
}
}
return count;
}
The sheet list is long at A2:A1000 but if this is an issue I could cut this down to 12 max and archive data every 12 weeks.
Anyone know how to make this work?
https://docs.google.com/spreadsheets/d/1Z-mU1VS-oxG0PDBzfn0ThyG0S4NxrhR3YL60ndzDY6E/edit?usp=sharing
One more approach you may test out:
=let(Σ,let(Λ,reduce({"",""},index(to_text(tocol(A2:A,1))),lambda(a,c,vstack(a,reduce({"",""},sequence(7),lambda(x,y,vstack(x,hstack(indirect("'"&c&"'!A2:A"),choosecols(indirect("'"&c&"'!B2:H"),y)))))))),
filter(Λ,index(Λ,,2)<>"")),
makearray(counta(C2:C),counta(D1:M1),lambda(r,c,countifs(index(Σ,,1),index(C2:C,r),index(Σ,,2),index(D1:M1,,c)))))
- Use this in Cell_D2 & it expands to right & downwards automatically. make sure there's no other data in this range blocking the formula.
- this also takes into account the data directly from the drop-downs from all the date_tabs
B:H& not fromM:V
