Multiple criteria sum formula

[BAHNTR]

I am needing assistance on creating a formula to sum a row if 2 criterias are met. There are 2 worksheets

Sheet #1
Acct YTD Total
1........?
2........ ?


Sheet #2

..............1 2 3 4 5 6
..............Y Y Y Y Y N
Account
1 ............1 2 3 4 5 6
2 ............7 8 9 3 4 5


I need the formula for the ? to say that if the period is Y then to sum the account row, ie. for account 1 it would sum 1-5 to give a total of 15.

 

[moonfish]

In excel 2007 or later you can use the sumifs formula. Else you can use an array function or you can combine the parameters with concatenate().

 

[BAHNTR]

I have tried sumifs and it is bring back #value error. I am unable to get it sum the row correctly. I am not familiar with an array function either.

 

[moonfish]

If you replace Y and N with true and false you can use it as a multiplicative.

=sumproduct(range1,range2) will return exactly what you need.