I have a spreadsheet where I need to count how many times a certain word appears, but only in columns that have a specific value in the header row.
Here’s my sample data:
Header
A
B
B
A
Sarah
X
Y
X
Y
Sarah
Z
X
Z
X
X
Sarah
Y
Sarah
Z
Z
Y
X
Y
X
I want to count how many times “Sarah” shows up, but only in columns where the header equals “A”. Looking at my example, I expect the result to be 3.
I tried using COUNTIFS but keep getting a VALUE error about mismatched array sizes. When I fix the array issue, I only get 1 as the result instead of 3. I also attempted DCOUNTA but that didn’t work either. I’ve had success with AVERAGEIFS for similar tasks before, but this text-based counting is giving me trouble.
Array formulas work better than SUMPRODUCT here. Try =SUM((A1:D1="A")*(COUNTIF(A2:D5,"Sarah"))) - just remember to hit Ctrl+Shift+Enter when you enter it. I’ve hit the same VALUE errors with COUNTIFS because it can’t handle conditional logic across different row ranges. You could also use INDEX and MATCH to find your “A” columns first, then run COUNTIF on just those ranges. The real problem is Google Sheets freaks out when COUNTIFS tries to evaluate header conditions and data conditions in one go. Split it into steps or use array formulas - that usually fixes the range mismatch issues.
SUMPRODUCT should work, but watch your range references. Try =SUMPRODUCT(--(A1:D1="A"),--(A2:D5="Sarah")) instead. The double negatives turn TRUE/FALSE into 1/0, which is way more reliable than multiplying arrays directly. I got the same VALUE error when I mixed different range sizes or when Sheets couldn’t handle the boolean logic. Also check that your headers and data rows line up properly - extra spaces or weird formatting can mess up comparisons. This formula saved me when COUNTIFS kept crashing on array mismatches.