Formula to SUM values in mulitple columns, based on criteria from another column

Question

I have a sheet with three columns. The first is called "Account Number" (AccNr). The two other contains values related to each account number. It looks like this:

AccNr. | Value 1 | Value 2
1000     1         2
2000     3         4
3000     5         6
3500     7         8
3600     9         0
4000     10        11
5000     12        13

What I want to do is to take "Value 1" and "Value 2" of any row where the account number is between 3000-3999. So in this case I would be taking the values from the row with account number 3000, 3500, and 3600. This would be the values 5, 6, 7, 8, 9, and 0.

I then want to add those values together to get a final number.

Are there any formulas for doing this kind of job?

Answer 1

You will want to use SUMIFS for this.

=SUMIFS(B2:B8,A2:A8,">="&3000,A2:A8,"<="&3999)+SUMIFS(C2:C8,A2:A8,">="&3000,A2:A8,"<="&3999)

The first part =SUMIFS(B2:B8,A2:A8,">="&3000,A2:A8,"<="&3999) will sum up any values in B2:B8, where the value in A2:A8 is greater than or equal to 3000, and where the value is ALSO less than or equal to 3999.

The second part +SUMIFS(C2:C8,A2:A8,">="&3000,A2:A8,"<="&3999) Then does the same but sums up form C2:C8.