Sum with two test ranges

[Fitch]

Hi there.

The formula below works fine for a complete total.

SUMIF(Ledger.C$8:C$50,B3,Ledger.F$8:F$50))

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?

[mariosv]

At least a couple of functions can solve the question.

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

[Fitch]

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!

[mariosv]

If the answer solves your question please tick the .