SUMIF with more than 1 column

[davidrsutton]

Hi, I'm sure that this is really quite straightforward, so any suggestions would be great.

I've got a table in excel that has two main categories.
One category - Budget area, is defined in a column at the beginning of the table.
I've also got all of my fiures split into months, this category is seperated along the top row of the table.

I want a formulae that will tell me what the sum of all the months is for a particular budget area.

I thought that I would just be able to use something along the lines of "=SUMIF(B:B,A1,C:N)" but this only sums the first column (C).
Other than adding a total column at the end of the table, has anyone got any ideas how I can get excel to sum all of the columns that it matches to A1.

Thanks in advance.

 

[SilentAiche]

David,

Would a PivotTable do the trick? What version of Excel are you using?

Best of luck!
Tim

[blue]______________________________________________________________
I love logging onto Tek-Tips. It's always so exciting to see what the hell I
said yesterday. [/blue]

 

[KenWright]

=SUM(OFFSET($B$1,MATCH(A1,$B:$B,0)-1,1,1,7))

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[davidrsutton]

Thanks for the suggestions - I had a play with the formulae that you suggested Ken and got it to work properly, so thanks for that.

Is there any way that this formulae can be used if there is more than one instance of the category (budget area) that is being matched though?

I know this might sound strange and it's hard to explain why without going into too much detail, but the same budget area categories can appear more than once within the table and I need to be able to add all of instances together.

Any ideas?

P.S. I forgot to menion in my first post that I'm designing this using office XP, but it has to be able to work on machines running excel 97.

 

[KenWright]

=SUMPRODUCT(($B$2:$B$200=$A$1)*($C$2:$N$200))

Can't use Full column references though

Reagrds
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------