Sumif with staggered columns

Question

I would like to add the rows if the value in each cell is less than 100 and if the columns headings were "Tom", "Dick" and "Harry"

So in Row 1, only Dick is less than 100 so the sum is 7.

In Row 2, Tom and Harry are less than 100 individually so the sum is 79.

I have over 30, 250x250 matrices where I would like to get conditional sums of a seven staggered columns. All the combinations of SUMIFs I have tried seem to be giving errors.

I don't just want to add, I would like to be able to do other things, like just count how many times Tom, Dick, and Harry have individually less than 100 or calculate other statistics like mean, median etc.

Answer 1

I would limit the criteria range to each row and then evaluate. This approach should work for COUNTIFS too (take them individual i.e. not aggregate the counts as we do with the SUM).

The formula for SUM would look like this:

=SUMIFS(B2:F2,$B$1:$F$1,"Tom",B2:F2,"<100")+
SUMIFS(B2:F2,$B$1:$F$1,"Dick",B2:F2,"<100")+
SUMIFS(B2:F2,$B$1:$F$1,"Harry",B2:F2,"<100")

So the formula logic is (this logic should work for Countifs too):

SUMIFS(B2:F2,$B$1:$F$1,"Tom",B2:F2,"<100")

SUMIFS(Return value from row, Search for "Tom", given that the return value is <100), we do the same for the other people too.

---

EDIT:

For the countif same logic tested:

=COUNTIFS($B$1:$F$1,"Tom",B2:F2,"<100")