Ask Your Question

sum with two test ranges

asked 2017-07-31 01:32:21 +0100

Fitch gravatar image

updated 2017-07-31 01:50:49 +0100

Hi there.

The formula below works fine for a complete total.


Ledger Column C is say "Sundry Sales, Kit Purchases, Room hire, etc"

Ledger Column F is the cost of various items.

B3 in this case is say "Sundry sales" and hey presto it works.

Unfortunately, I've got about 3 years transaction in the same ledger (it's for a club, we only meet once a month). Column A is the transaction date.

What is the possibility of just getting one years worth, i.e how could I alter the formula to include only the transactions in Year(A8) - which happens to be 2015 - the year the club started, then I could just do YEAR(A8)+1 for the next column.

Is this a possibility?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-07-31 02:22:43 +0100

m.a.riosv gravatar image

At least a couple of functions can solve the question.

image description

With SUMIFS() to get the YEAR() working on the column, needs to be introduce as array [Ctrl+Shift+Enter] instead of [Enter] or involving it with SUMPRODUCT() to forze the array only with [Enter].

My preference when there is no need of regular expression/wildcards is with SUMPRODUCT() looks clear for me how the conditions are and it's more flexible to use functions inside it.

Sample file SumWithSeveralConditions.ods

edit flag offensive delete link more


Thank you very much That worked a treat. I've used the 3rd one as it's the shortest. It'll take a few days for me to figure out how sumproduct works, as I thought that was only to multiply things. Cheers!

Fitch gravatar imageFitch ( 2017-07-31 03:40:50 +0100 )edit

If the answer solves your question please tick the ✔.

m.a.riosv gravatar imagem.a.riosv ( 2017-07-31 19:50:15 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-07-31 01:32:21 +0100

Seen: 32 times

Last updated: Jul 31 '17