I’m working on a spreadsheet where column A has categories like ‘apple’ or ‘banana’ and column B has time values. I want to add up all the times for a specific category.
I figured out how to get the rows I need:
=QUERY(FRUIT!A:Z, "SELECT A where A = 'apple'")
But I’m stuck on how to grab the matching B values and add them up. Any ideas on how to do this? I’m pretty new to formulas so a simple explanation would be great.
Thanks for any help!
hey alexj, you’re close! try SUMIF function. it’ll do the trick for ya. format is like this:
=SUMIF(A:A, “apple”, B:B)
this’ll add up all B values where A is “apple”. hope it helps! lemme know if u need more info
I’ve been in a similar situation, and I found the SUMIF function to be incredibly useful. However, if you’re dealing with more complex criteria or multiple categories, you might want to explore the SUMIFS function. It’s like SUMIF on steroids.
For your specific case, you could use:
=SUMIFS(B:B, A:A, “apple”)
This allows you to add more criteria easily if needed in the future. For instance, if you later want to sum times for apples only on weekdays, you could add another condition without changing your formula structure.
Just a tip from personal experience: keep an eye on your data types. Make sure your time values in column B are actually recognized as times by Google Sheets, or you might get unexpected results when summing.
While SUMIF is a solid choice, I’ve found QUERY to be more versatile for tasks like this. You can modify your existing formula to sum the B values:
=SUM(QUERY(A:B, “SELECT B WHERE A = ‘apple’”))
This approach is particularly useful if you need to perform more complex operations later. It’s also more efficient for large datasets.
One caveat: ensure your time values in column B are properly formatted as times. If they’re text, you might need to convert them first using the TIME function. This can trip up even experienced users.