sumif with one criteria column, 3 sum columns?

[taigovinda]

Hi,

I want to have a sumif that reads the criteria from just one column, but adds up the numbers from three columns if the criteria is correct.

Currently I have named ranges defined as:

Dept_Line =OFFSET(Essbase!$R$1,0,0,COUNTA(Essbase!$R:$R)+12,1)
Dept_Data_1 =OFFSET(Dept_Line,0,-15,,3)

So "Dept_Line" is the criteria column and "Dept_Data_1" is 3 columns that I want to sum if the criteria matches.

The formula I am trying to use looks like this:
=SUMIF(Dept_Line,$A8,Dept_Data_1)

What I want is, for example, if A8="pick_me", and in one row "Dept_Data_1" has 3,2,5 and "Dept_Line" has "pick_me" then I want my formula to return 3+2+5=10. Currently it is returning only 3... I could just add up 3 different sumif's, but then I need to name 3x as many ranges!

Any ideas?

Thanks!!!

Tai

 

[Richard Schollar]

Hi Tai

Try:

=SUMPRODUCT((Dept_line=$A8)*Dept_Data_1)

 

[Aladin Akyurek]

Control+shift+enter

=SUM(IF(Dept_Line=$A8,Dept_Data_1))

Better:

Create a total column that calculates a subtotal for every record in Dept_line and change the definition

Dept_Data_1 =OFFSET(Dept_Line,0,-15,,3)

to:

Dept_Data_1 =OFFSET(Dept_Line,0,-15,,4)

Then invoke:

=SUMIF(Dept_Line,$A8,INDEX(Dept_Data_1,0,4))

 

[taigovinda]

Thanks to both of you for the replies. I realized that I need to also refer to the three columns individually after all, so I'll just name them all. However these techniques will be very handy for me shortly!

Thanks again!

Tai