SUMIF for multiple rows

**nick.martin857** · 08-07-2014, 04:01 PM

Hi all,

I want to sum multiple cells in a column if the title is a certain value.

A B C D E
1 2008 2009 2010 2011 2012
2 23 45 65 32 11
3 10 10 10 24 22
4 32 43 24 76 43
5 82 13 57 99 34
6 12 43 21 88 56

I wanted a value for row 2, year 2008 through 2012 separately
I also wanted a value for row 3 for each year.

The problem arises when I want row 4-6 summed for each year.

I am linking to another tab which specifies the year. on the left and the category (rows) on the top.

Thanks in advance!

**FDibbins** · 08-07-2014, 04:02 PM

Hi, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**Tony Valko** · 08-07-2014, 04:04 PM

Not real clear.

Suggest you post a SMALL sample file and show us what results you expect.

**ChemistB** · 08-07-2014, 04:18 PM

Okay, for table two, in the first row I put the min row you want summed (i.e. 2 or 4) and in row 2 I put the maximum row you want summed (i.e. 2 or 6)
Then the formula in H3 that can be copied across and down is

=SUM(INDEX($A$2:$E$6,MATCH(H$1,{2,3,4,5,6},0),MATCH($G3,$A$1:$E$1,0)):INDEX($A$2:$E$6, MATCH(H$2,{2,3,4,5,6},0),MATCH($G3,$A$1:$E$1,0)))
See attached.