I have a spreadsheet where column A contains categories (like ‘x’ or ‘y’) and column B contains time durations. I need to find all rows where column A equals ‘x’ and then sum up all the corresponding duration values from column B.
I can filter the rows using this query:
=QUERY(DATA!A:Z, "SELECT A where A = 'x'")
But I’m stuck on how to get the matching values from column B and add them together. What’s the best way to calculate the total duration for all rows that match my criteria? I’ve tried a few approaches but can’t seem to get it working properly.
Had the same issue last month tracking project hours. QUERY can filter and sum at the same time. Instead of just selecting column A, use =QUERY(DATA!A:Z, "SELECT SUM(B) WHERE A = 'x'") - it’ll give you the total directly without extra functions. Just make sure column B duration values are formatted as numbers or SUM won’t work. Way cleaner than SUMIF for bigger datasets, plus you can add complex conditions later.
honestly just go with =SUMIFS - you’ll thank me later when you need more flexibility. it’s like SUMIF but handles multiple criteria way better. for what you’re doing, =SUMIFS(DATA!B:B, DATA!A:A, "x") does the exact same thing as SUMIF but won’t leave you stuck when you inevitably need more conditions. trust me, i’ve been there - constantly redoing formulas sucks.
You’re overcomplicating this. Skip QUERY and just use SUMIF - it’ll do exactly what you need. Try =SUMIF(DATA!A:A,"x",DATA!B:B) - it checks column A for ‘x’ and sums the matching values from column B. I use this all the time for stuff like this. Way simpler than mixing QUERY with SUM functions. The syntax is SUMIF(range_to_check, criteria, sum_range) so you can tweak the criteria easily. Plus it’s faster since it doesn’t filter first.