for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

ChrisHB Jul 2024 3:05pm, 14 Jul 2024 23,134 posts	Quote Pin ChrisHB Anyone familiar with the SQL query function on Google sheets? if I have a column containing A B A B A then in SQL I can write select column, count() from table group by column* and get the answer A 3 B 2 Google sheets has SELECT and COUNT and GROUP BY and doesn't need FROM TABLE but I am at a loss to know how to get my desired result.
ChrisHB Jul 2024 7:05pm, 14 Jul 2024 23,136 posts	Quote Pin ChrisHB I've almost found a way - with any luck it'll not be a cul-de-sac!
larkim Jul 2024 7:27pm, 14 Jul 2024 25,112 posts	Quote Pin larkim I've used SQL within an =htmlimport() function I think in GSheets, I think you wrap it all in a =query() function don't you?
NDWDave Oct 2024 6:17pm, 5 Oct 2024 20,122 posts	Quote Pin NDWDave one from the archive
GlennR Oct 2024 8:50pm, 5 Oct 2024 76,619 posts	Quote Pin GlennR
run free Oct 2024 9:11pm, 7 Oct 2024 5,371 posts	Quote Pin run free Why did the Excel file go to therapy? Because it had too many unresolved cells! 😄
GeneHunt59 Oct 2024 11:14am, 9 Oct 2024 3,878 posts	Quote Pin GeneHunt59 Hi. I'm trying to create a formula that will do the sum of the 10 highest values in a row for my running club's championship spreadsheet. It's straightforward if nobody runs more than 10 races to use the sum function, but some run more than 10. I've tried the following formula: =SUM(LARGE(C2:V2, {1,2,3,4,5,6,7,8,9,10})) This is fine if a member runs 10 or more races, but if they run 9 or less it returns #NUM! I need a formula that returns the total if there are 10 or more or less than 10 races ran, so need to add a condition? Any help much appreciated
larkim Oct 2024 11:21am, 9 Oct 2024 25,769 posts	Quote Pin larkim From a quick google, this might help =SUMPRODUCT(LARGE(range, ROW(INDIRECT("1:"&MIN(n, COUNT(range)))))) e.g. if race results are in columns A:F and you want a max of 4 entries counting:- =SUMPRODUCT(LARGE(A1:F1, ROW(INDIRECT("1:"&MIN(4, COUNT(A1:F1))))))
CBN Oct 2024 11:26am, 9 Oct 2024 121 posts	Quote Pin CBN Would =IF(COUNT(C2:V2)<10,SUM(C2:V2),SUM(LARGE(C2:V2,SEQUENCE(10)))) work? Alternatively, =IFERROR(SUM(LARGE(C2:V2, {1,2,3,4,5,6,7,8,9,10})),SUM(C2:V2))
um Oct 2024 11:42am, 9 Oct 2024 8,925 posts	Quote Pin um Would changing your formula to inlcude "" or zero in the {string set} solve it - with or without converting blanks in the row to zeroes?