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
(I.e. Answer = 4)

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
View this thread: http://www.excelforum.com/showthread...hreadid=542080

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" wrote in
message ...

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
(I.e. Answer = 4)

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
View this thread: http://www.excelforum.com/showthread...hreadid=542080

SUMIF using 2 Criteria in 2 columns
 

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


--
quailc
------------------------------------------------------------------------
quailc's Profile: http://www.excelforum.com/member.php...o&userid=34442
View this thread: http://www.excelforum.com/showthread...hreadid=542080

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.

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
View this thread: http://www.excelforum.com/showthread...hreadid=542080

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" wrote in
message ...

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
View this thread: http://www.excelforum.com/showthread...hreadid=542080

SUMIF using 2 Criteria in 2 columns
 

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


=(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


--
quailc
------------------------------------------------------------------------
quailc's Profile: http://www.excelforum.com/member.php...o&userid=34442
View this thread: http://www.excelforum.com/showthread...hreadid=542080