Sum of a range based on 2 different criteria?

Our take

Calculating the sum of a range based on two different criteria is a common challenge in spreadsheet management, particularly for tracking holiday funds. In your case, you're looking to automate the sum for a specific person's remaining balance according to a designated year. By leveraging functions like SUMIFS, you can effectively filter the data to match both the individual's name and the year. This approach eliminates the need for manual cell selection, allowing your document to update dynamically as new entries are added.

Hello,

I'm working on a document which is a holiday funds to keep track of individual person's remaining balance to pay.

I want to use a formula to automatically calculate the sum of a range of cells which lie against a certain year under a person's name.

Below as an example:

https://preview.redd.it/1p19n1o8qrxg1.png?width=991&format=png&auto=webp&s=2e67c0337da92ae1feffe43fc2c7fbaba237d672

As above, I want to look up Name 1 in 1:1, look up Year 1 in A:A and then return the sum of those numbers in that Year 1 range to give me my final result.

Is this possible?

I've tried all sorts of SUM, SUMIF, SUMIFS, XLOOKUP and combinations but can't seem to get it to work. I also don't want to have to manually select the cells for year SUM as this is constantly updating and want it to be automated.

Thank you!

submitted by /u/celestialspace
[link] [comments]

Read on the original site

Open the publisher's page for the full experience

View original article →

Tagged with

#Excel alternatives for data analysis#rows.com#natural language processing for spreadsheets#AI formula generation techniques#generative AI for data analysis#automated anomaly detection#Excel compatibility#formula generator#Excel alternatives#cloud-based spreadsheet applications#sum#holiday funds#formula#Excel#remaining balance#lookup#year#range of cells#automatically calculate#SUMIF