Problem with SUMIF

[dk]

Hello,

I've set up a profit and loss reporting tool in Excel which is working well. Part of the remit for this project was that I wasn't to use any macros or UDFs (hard for me!) Anyway, I've been using a combination of OFFSETs, INDIRECTs and all sorts of other functions to get what I need but have come across a problem while using SUMIF. Here are the details:-

Worksheet 1H Actuals contains a ReportingLine such as sales, insurance, tax in column B. Columns C through N contain the monthly total for Jan-Dec for each reporting line.

Worksheet P&L contains this formula:-

=SUMIF('1H Actuals'!$B$2:$B$300,$A12,'1H Actuals'!$C$2:$F$300)

I wanted this to return the total for the year for the reporting line contained in A12. The problem is that it's only returning the totals from column C (i.e. January) not the whole year.

There's nothing in the help file to suggest that this should be the case. Does anyone know how I can do this (preferably without using an array formula although if that's the only way then so be it).

Thanks for reading this long post. If I'm being thick then please forgive me - I've been up since 5am, it's now 9pm and I've been working on this crap all day

Dan

[Aladin Akyurek]

On 2002-05-15 13:03, dk wrote:
Hello,

I've set up a profit and loss reporting tool in Excel which is working well. Part of the remit for this project was that I wasn't to use any macros or UDFs (hard for me!) Anyway, I've been using a combination of OFFSETs, INDIRECTs and all sorts of other functions to get what I need but have come across a problem while using SUMIF. Here are the details:-

Worksheet 1H Actuals contains a ReportingLine such as sales, insurance, tax in column B. Columns C through N contain the monthly total for Jan-Dec for each reporting line.

Worksheet P&L contains this formula:-

=SUMIF('1H Actuals'!$B$2:$B$300,$A12,'1H Actuals'!$C$2:$F$300)

I wanted this to return the total for the year for the reporting line contained in A12. The problem is that it's only returning the totals from column C (i.e. January) not the whole year.

There's nothing in the help file to suggest that this should be the case. Does anyone know how I can do this (preferably without using an array formula although if that's the only way then so be it).

Thanks for reading this long post. If I'm being thick then please forgive me - I've been up since 5am, it's now 9pm and I've been working on this crap all day

Dan

Dan,

Either you have to chain up SUMIFs to consider every range to sum or use

=SUMPRODUCT(('1H Actuals'!$B$2:$B$300=$A12)*('1H Actuals'!$C$2:$F$300))

Aladin

[dk]

Aladin,

Simple and elegant - you're a STAR!

Thanks,
Dan