Question.ods (34.0 KB)
I have a column with years in one (2023,2024 and 2025).
What I trying to do is get a total for the “deposit” or positive number based on the year.
I can get it by doin a range, =SUMIF(B6:B30,">0"), but I cannot figure out how to do it
by the year.
Thanks ahead for any suggestions.
If you don’t want to worry about the range to be summed use a formula such as
=SUMIFS(B:B;B:B;">0";A:A;">="&E3&"-01-01";A:A;"<="&E3&"-12-31")
When using column references, Sumifs shortens the calculation on the last row with data, while Sumproduct does not. Moreover, it is easy to copy formulas without worrying about the relative/absolute reference of the columns to be searched or summed.
Sumproduct is really powerful and allows to prepare criteria that are impossible with other functions, but this non shortcut makes calculations with columns slow, especially if there are several formulas doing it.
Mariosv, when I use your function, I get a “err:501”
Steph1, it just what I need. Thanks
Steph1
Thank you, it works fine for me.
This is a repeat, I could not delete it.
Sorry, the quotes changed at pasting in the comment. Updated.