SUM Function Works Fine in One Sub-Sheet(?) but Not in the Other?

Question

I have a SUM function that works fine in one sub-sheet(the tabs default titled sheet1, sheet2, etc. on the bottom of the screen) but decides not to work in the other. This is what the function looks like:

=SUM(QUERY($A$1:$B$5, "SELECT B WHERE A = '" & "s" & "'", 0))

This function works just fine, but when I move it to the first sub-sheet and change the reference cells, it stops working and outputs only zeros.

*="Average: " & DIVIDE(* SUM( QUERY( $B$2:$M, "SELECT K WHERE B = '" & "s" & "'", 0)), COUNTIF( $B$2:$B, "S"))

When I isolate the SUM function, like so:

=SUM(QUERY($B$2:$M, "SELECT K WHERE B = '" & "s" & "'"))

It returns a zero. I had tested to see if the reference cells were the issue by using the following function, but that had worked just fine.

=SUM($K$2, $K$3)

So yeah, I have no idea what it is I'm doing wrong. I have tried refreshing the page and nothing changed, so it's most likely something I'm doing. I will link a copy of the sheet in question. The cells with the problem formula and various test attempts are N5, N35, N36, N37 in sheet1 and D2, D3, F10, D13, D14 in sheet2.

https://docs.google.com/spreadsheets/d/1bNmDYdUw-FU4wWTShV1cijtOt9l7McvktgAoDELbwQ8/edit?usp=sharing

The purpose of this function is to add all numbers in column K where the cell in the same row, in column B, contains "s" so that the resulting total can be divided by the number of cells containing "s". Basically, I'm trying to find the average(yes, I tried the AVERAGE function. Same results)

If any of you guys have any ideas as to what it is I'm doing wrong, I would seriously appreciate the help!

Edit: For some reason, when I change the Query range from $B$2:$M to $B$2:$M$5 it works? Am I perhaps asking too much of the Query function with a range that large? Is there any way to change this?

Answer 1

Column K looks like this:

PA Score
14.625
14.985
-
-
-
-

The majority of the values in the column consist of the text string -. The query() function only allows one data type per column, and decides the column type by looking at the majority of the values in the first 100 rows.

The column type of is therefor string, and the two values your query() formula returns are text strings that look like numbers. The sum() function ignores text strings.

To make it work, use something like this:

="Average: " & averageif(B$2:B, "s", K$2:K)

...or the average(iferror(filter())) pattern.