Sum if array formula, with multi criteria, some in the same column

**carlosmaldonado** · 10-11-2012, 03:19 PM

Hi guys,

Hope there's a few excel master's that could help me building an array formula for the following:

I've a file, in which I want to sum, in another sheet, a column of material values, by taking in consideration that this sum should be only for a specific business area, that info is in other column, and then taking in consideration that shall be cumulative monthly, and the months are in other column. But for this last array I want that the formula sum more than one month.

How do I do it?

Formulas in Portuguese. Excel 2010. excelforum_test.xlsx

I'm using: {=SOMA(SE((Data!$H2:$H30="RMV"'this is the business area column')*(Data!$B2:$B30="ABR"'this is the first month column')*(Data!$B2:$B30="MAI"'this is the second month column');Data!$U2:$U30'this is the rang I want to sum'))}

What is wrong here?

Sometimes, simple things becomes stupidly difficult to do!

Regards

**NBVC** · 10-11-2012, 03:32 PM

Try instead:

=SOMA(SE((DATA!$H2:$H30=B$1)*((DATA!$B2:$B30="ABR")+(DATA!$B2:$B30="MAI"));DATA!$U2:$U30))

confirmed with CTRL+SHIFT+ENTER

or

=SOMARPRODUTO(SOMA.SE.S(DATA!$U2:$U30;DATA!$H2:$H30;B$1;DATA!$B2:$B30;{"ABR";"MAI"}))

This formula is not an array formula, so you can use just ENTER.