SUMIF using 2 Criteria in 2 columns

1. SUMIF using 2 Criteria in 2 columns

I would be grateful if someone could offer their advice on how to solve a problem I am having with XL.

I am trying to sum all the values in column C if column A and Column B meet the correct criteria.

If Column A = X and Column B = B then Sum all the corresponding values in Column C.

Column A Column B Column C
X B 1
Y B 7
Z C 2
X B 3
Y C 10

My Excel book quotes the formula below:
I.e. {SUM((A:A="X")*(B:B="B)*(C:C))}

This formula does not look at the whole Column A only the cell in the same row as the formula.

How can I get the formula to look at the entire array of data and return the sum of all the column C items that meet the correct conditions in Columns A &B?

2. Re: SUMIF using 2 Criteria in 2 columns

=SUMPRODUCT(--(A1:A200="X"),--(B1:B200="B"),C1:C200)

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"quailc" <quailc.27ukd1_1147693502.8303@excelforum-nospam.com> wrote in
message news:quailc.27ukd1_1147693502.8303@excelforum-nospam.com...
>
> I would be grateful if someone could offer their advice on how to solve
> a problem I am having with XL.
>
> I am trying to sum all the values in column C if column A and Column B
> meet the correct criteria.
>
> If Column A = X and Column B = B then Sum all the corresponding values
> in Column C.
>
> Column A Column B Column C
> X B 1
> Y B 7
> Z C 2
> X B 3
> Y C 10
>
> My Excel book quotes the formula below:
> I.e. {SUM((A:A="X")*(B:B="B)*(C:C))}
>
> This formula does not look at the whole Column A only the cell in the
> same row as the formula.
>
> How can I get the formula to look at the entire array of data and
> return the sum of all the column C items that meet the correct
> conditions in Columns A &B?
>
>
> --
> quailc
> ------------------------------------------------------------------------
> quailc's Profile:

http://www.excelforum.com/member.php...o&userid=34442
>

3. Bob,

Many Thanks for your advice with this. The SUMPRODUCT function works perfectly! You have saved me an awful lot of time messing around with this.

Kind regards
Corinne

4. I would be grateful if someone could offer their advice on how to solve a problem I am having with XL.

Is it possible to perform the SUMPRODUCT function on a data range that is in a seperate workbook?

I have tried selecting the range from another workbook but it comes back with #value result.

How do I link the 2 workbooks together for this to work correctly?

5. Re: SUMIF using 2 Criteria in 2 columns

Yes you can. Show us what you have tried.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"quailc" <quailc.27uoj0_1147698903.2754@excelforum-nospam.com> wrote in
message news:quailc.27uoj0_1147698903.2754@excelforum-nospam.com...
>
> I would be grateful if someone could offer their advice on how to solve
> a problem I am having with XL.
>
> Is it possible to perform the SUMPRODUCT function on a data range that
> is in a seperate workbook?
>
> I have tried selecting the range from another workbook but it comes
> back with #value result.
>
> How do I link the 2 workbooks together for this to work correctly?
>
>
> --
> quailc
> ------------------------------------------------------------------------
> quailc's Profile:

http://www.excelforum.com/member.php...o&userid=34442
>

6. I am trying to link my current workbook with:
Workbook name: Leave Planner Apr 06 - Nov 06 working.xls
Worksheet: May 06

(They are saved in the same folder)

=(SUMPRODUCT(1*('[Leave Planner Apr 06 - Nov 06 working.xls]May 06 '!\$A\$41:\$A\$68="A&C Cert"),1*('[Leave Planner Apr 06 - Nov 06 working.xls]May 06 '!\$D\$40="E")*'[Leave Planner Apr 06 - Nov 06 working.xls]May 06 '!\$D\$41:\$D\$68))

Thank you
Corinne

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1