CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Beee
Beee

Reputation: 49

Referring to a specific cell while using Query

I've got a data range that has columns with chapters, dates, word count etc. but when I try to query this range and group it by a specific chapter range while referring to specific cells, my formula doesn't work correctly.

=sort(query('by novels'!C2:H, "select (G),(H),(F), SUM(F) where (D) >= '"&E4&"' and (D)<= '"&E5&"' Group By (G),(H),(F)",0))

where E4 and E5 are cells that are supposed to indicate the starting and ending chapter range.

The formula works correctly if I physically type the numbers in where (D) >= 270 and (D)<= 350 Group By (G),(H),(F)",0)), but when I use first formula, it just returns my headers and no other data at all.

The cells are highlighted like when they're selected though.

Does anyone know what I'm doing wrong?

Upvotes: 0

Views: 117

Answers (2)

Erik Tyler
Erik Tyler

Reputation: 9345

Remove the four single quotes from your formula. Those are telling QUERY that you are looking for strings when you are actually looking for numbers.

You also don't need parentheses to refer to a column alone. Though this won't affect functionality, it may affect readability.

So this should correct the issue:

=SORT(QUERY('by novels'!C2:H, "select G, H, F, SUM(F) WHERE D >= "&E4&" and D<= "&E5&" GROUP BY G, H, F",0))

If not, consider sharing a link to your spreadsheet or a copy of your spreadsheet.

Upvotes: 1

idfurw
idfurw

Reputation: 5852

Try this. Numbers should not be quoted by ''

=sort(query('by novels'!C2:H, "select (G),(H),(F), SUM(F) where (D) >= "&E4&" and (D)<= "&E5&" Group By (G),(H),(F)",0))

Upvotes: 0

Related Questions