# sumif with one criteria column, 3 sum columns?

#### taigovinda

##### Well-known Member
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

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### Richard Schollar

##### MrExcel MVP
Hi Tai

Try:

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

##### MrExcel MVP
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

##### Well-known Member
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

Replies
4
Views
430
Replies
10
Views
648
Replies
5
Views
1K
Replies
3
Views
343
Replies
3
Views
656

1,190,811
Messages
5,983,046
Members
439,816
Latest member
aggelos

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back