I’m working with a Google Sheets workbook that has separate tabs for each weekday plus a summary tab for weekly calculations. Each daily worksheet contains a dropdown menu in cell D25 where I can select either ‘yes’ or ‘no’.
On my summary worksheet, I want to create a formula that counts how many times ‘yes’ was selected across all the daily tabs. I’m trying to use COUNTIF but I’m struggling with the correct syntax to reference the same cell location from multiple sheets.
I attempted these formulas but they’re not working: =countif(Monday!D25:Friday!D25,'yes') =countif('Monday!D25,Tuesday!D25,Wednesday!D25,Thursday!D25,Friday!D25', 'yes')
What’s the proper way to count values from the same cell across different tabs in Google Sheets?
COUNTIF cannot directly reference the same cell across multiple sheets, which I’ve encountered before when managing project statuses. Instead, consider using SUMPRODUCT: =SUMPRODUCT((Monday!D25="yes")+(Tuesday!D25="yes")+(Wednesday!D25="yes")+(Thursday!D25="yes")+(Friday!D25="yes")). Alternatively, you could set up a helper column in your summary sheet that references each D25 value individually, such as =Monday!D25, =Tuesday!D25, and so on, allowing you to then apply COUNTIF on that range. This method can simplify troubleshooting if a sheet has data issues, and both approaches are effective depending on your preference.
I’ve had good luck with the QUERY function for this - it’s way more flexible than other methods. Try =QUERY({Monday!D25;Tuesday!D25;Wednesday!D25;Thursday!D25;Friday!D25},"SELECT Col1 WHERE Col1='yes'",0) then wrap it with ROWS to get your count: =ROWS(QUERY({Monday!D25;Tuesday!D25;Wednesday!D25;Thursday!D25;Friday!D25},"SELECT Col1 WHERE Col1='yes'",0)). This really shines when you need complex conditions later or want to count different values. QUERY handles empty cells better than most functions, plus you can tweak the criteria without rebuilding the whole formula.
try this workaround - it worked for me. create a formula like =COUNTIF({Monday!D25;Tuesday!D25;Wednesday!D25;Thursday!D25;Friday!D25},"yes") using array syntax with semicolons. the curly braces combine those cells into one range so COUNTIF can handle it properly.