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.
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.
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$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?