Sumproduct Formula Help

[Sumoproduct]

Hello!

I'm having some trouble getting a simple Sumproduct function to work. Maybe there's a better function to tackle my problem?

I'm creating a financial planner for an organization, and I need to be able to display all previous costs (spanning over multiple months and years), excluding the current month.

Currently I have =SUMPRODUCT((J10:J12<>RefYear)*(J10:J12=RefYear&K10:K12<>RefMonth)*L10:L12), where the J column is my years, K is months, and L is the costs. Also, I just wrote in the Refs where my reference cells usually are for the current month and year.

Basically, I want the sum of all costs, excluding the month for the current year.

Help? I also may not be using the ampersand correctly, but I'm not sure how to create one array that fits two criteria.

[Melvinrobb]

Does this work?

Please Login or Register  to view this content.

[Sumoproduct]

Wouldn't that exclude the RefMonth from previous years?

[Melvinrobb]

Basically, I want the sum of all costs, excluding the month for the current year.

a couple of ways to interpret your request, but now I think I understand.
the formula below should work. I broke it up into two sumproducts:
The first sumproduct finds the totals from the current year, excluding the current month. The second sumproduct finds the total from previous years (no month filter applied).

Please Login or Register  to view this content.

[Sumoproduct]

Ha, thank you! A very simple solution, but it just couldn't figure it out in the moment. Thanks for your help!

[FDibbins]

or perhaps using sumifS()?

=SUMIFS(L10:L12,J10:J12<>RefYear,J10:J12=RefYear,K10:K12<>RefMonth)

)or something like that - hard to put it together without seeing the data it is using