SUMIF over multiple columns

**SueJ** · 05-24-2010, 08:58 PM

I am using Excel 2003 and have a file with two worksheets - one contains raw data about our clients, the other various sections designed to track different aspects of our business.

The problem I am having is with one area we want to track ... total income derived against the topics clients request us to cover in seminars or projects.

There are instances where clients want to focus on more than one topic .. frequently two and in some cases three. So I have three columns in the raw client data worksheet for Topic A, Topic B and Topic C.

In the business data analysis worksheet I want to use a formula that will check across all three columns for a particular topic, and if it's present sum another column of income derived.

Here is the formula I've used:

=SUMIF('Client Raw Data'!$J$2:$L$154,"Leadership",'Client Raw Data'!$I$2:$I$154)

However the formula only appears to take into account the data entered into the first column "J" in calculating the result, ignoring topics listed in K or L columns.

Any help would be very much appreciated!

Cheers
Sue

**NBVC** · 05-24-2010, 09:02 PM

Perhaps?

=SUMPRODUCT(('Client Raw Data'!$J$2:$L$154="Leadership")*('Client Raw Data'!$I$2:$I$154))

**SueJ** · 05-25-2010, 09:56 AM

Originally Posted by NBVC

Perhaps?

=SUMPRODUCT(('Client Raw Data'!$J$2:$L$154="Leadership")*('Client Raw Data'!$I$2:$I$154))

You are a life saver!

Many thanks - it worked perfectly.

I've not used SUMPRODUCT before - will have to remember this one.

Thank you so much for your help - it is VERY much appreciated.

Cheers
Sue