I’m currently trying to count scores ranging from 1 to 5 in my Google Sheets. While I can easily use COUNTIF to get counts of particular scores like 5s or 4s, there’s a catch.
I need to distinguish between cells in the top row that begin with a space and those that do not when counting the values. Unfortunately, using the COUNTIFS formula isn’t working because the ranges I’m using are of the same size.
Here are the formulas I’m working with:
For cells that start with a space:
=COUNTIFS(B1:F1,=" *",B2:F5,"=5")
For cells that do not have a space:
=COUNTIFS(B1:F1,="<> ",B2:F5,"=5")
Since the number of columns that contain headers with leading spaces varies among my forms, I’m looking for a general formula that could adapt to different sheets.
Is there any alternative to COUNTIFS I can use to handle this situation? Perhaps utilizing SUMPRODUCT or other array functions might work?
Had the same issue with dynamic ranges recently. Try MMULT with boolean arrays - it handles the range mismatch perfectly. Use =MMULT(--(LEFT(B1:F1,1)=" "),--(TRANSPOSE(B2:F5=5))) to count intersections where headers start with space and values equal 5. The double negatives flip TRUE/FALSE to 1/0 so they multiply properly. MMULT handles array operations internally, so it works regardless of sheet size. More complex than SUMPRODUCT but way better performance on large datasets. For headers without spaces, just swap the equals to not equals in the first part.
I’ve hit this header filtering issue before. SUMPRODUCT works great for conditional counting with mixed range sizes. Try =SUMPRODUCT((LEFT(B1:F1,1)=" ")*(B2:F5=5)) to count where headers start with a space, or =SUMPRODUCT((LEFT(B1:F1,1)<>" ")*(B2:F5=5)) for the opposite. LEFT checks the first character, then multiplies the arrays together. ARRAYFORMULA with conditional logic also works but gets messy fast. SUMPRODUCT’s more reliable since it handles arrays internally - no exact range matching like COUNTIFS needs.
totally agree, SUMPRODUCT’s pretty handy! you could also try using FILTER like this: =COUNTA(FILTER(B2:F5, LEFT(B1:F1,1)=" ", B2:F5=5)) - this will count the 5s where the header has a space. much simpler than COUNTIFS for sure!