I’m working with a Google Sheets document where I need to count ratings from 1 to 5, but with a twist. I can easily use COUNTIF to count how many times each rating appears, but I need to separate the counts based on whether the header row starts with a space character or not.
The issue I’m running into is that my COUNTIFS formula doesn’t seem to work properly since I’m dealing with arrays of the same size. Here are the formulas I’ve been trying:
For headers that start with a space:
=COUNTIFS(A1:E1," *",A2:E6,"4")
For headers that don’t start with a space:
=COUNTIFS(A1:E1,"<> ",A2:E6,"4")
I have multiple sheets in my workbook and each one has a different number of columns that start with spaces. I’m looking for a formula that can work across all sheets without needing manual adjustments for each one.
Is there an alternative approach I can use instead of COUNTIFS to solve this problem? Maybe using SUMPRODUCT or another function that might handle this scenario better?
I’ve hit the same wall with COUNTIFS and header conditions. COUNTIFS chokes on this because it wants single criteria ranges, not the array comparisons you need for prefix matching. What worked for me was switching to SUMPRODUCT with EXACT for cleaner text matching. Try =SUMPRODUCT(EXACT(LEFT(A1:E1,1)," ")*(A2:E6=4)) for those space-prefixed headers. It dodges the weird quirks you get with comparison operators in array formulas. If you can mess with your sheet layout, helper rows work great too. Just add a row under your headers that flags which columns start with spaces, then point your SUMIFS at that helper row. Way easier to scale across sheets since you only tweak the helper formula once per sheet.
try using SUMPRODUCT - for space headers you can do =SUMPRODUCT((LEFT(A1:E1,1)=" ")*(A2:E6=4)), and for non-space just tweak it to =SUMPRODUCT((LEFT(A1:E1,1)<>" ")*(A2:E6=4)). it handles arrays way better than COUNTIFS.
Try array formulas with wildcards instead of wrestling with COUNTIFS. For space-prefixed headers, use =SUMPRODUCT(--(LEFT(A1:E1&"",1)=" "),--(A2:E6=4)). The double negatives turn boolean results into numbers that SUMPRODUCT handles better. For non-space headers: =SUMPRODUCT(--(LEFT(A1:E1&"",1)<>" "),--(A2:E6=4)). Adding the empty string fixes errors from blank cells. I’ve found this approach way more reliable when column counts vary across sheets. Throw an IFERROR around it if you want to catch empty or mismatched ranges.
COUNTIFS can’t handle this array logic - you’re asking it to do two things at once and it breaks.
SUMPRODUCT works better, but honestly? I’d automate this whole thing instead of wrestling with formulas.
I had the same nightmare last year with financial reports - different column structures across departments. Wasted hours debugging formulas that broke whenever someone added a column.
Solved it by setting up automation that reads the Google Sheets data, processes header conditions in code, and writes results back. No more formula debugging or manual fixes when sheet structures change.
The automation checks each header for space prefixes, counts ratings automatically, and handles any number of columns. Add new sheets or change structures? It adapts.
You can also extend it for other analysis without cramming more complex formulas into your sheets.
Latenode makes this easy with their Google Sheets integrations. Build the logic once and it works across all sheets.