I need to sum a column based on two different variables. For example I need
to sum Column A if Column B = 123 and Column C = XD. For the example below
the sumif formula would equal "11." I have tried creating this formula
several times but I have yet to succeed as I have always gotten some type of
error. I could use the dsum formula but I would like to stick to"if"
formulas. Any suggestions?

Col. A Col. B Col.C
1 123 XD
5 222 XD
10 123 CC
10 123 XD

SUMPRODUCT is the way to go here.

=SUMPRODUCT(--($B$2:$B$100=123),--($C$2:$C$100="XD"),($A$2:$A$100))

Note, I am assuming all values in column A and column B are numbers, not text.
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Rody2003" wrote:

I need to sum a column based on two different variables. For example I need
to sum Column A if Column B = 123 and Column C = XD. For the example below
the sumif formula would equal "11." I have tried creating this formula
several times but I have yet to succeed as I have always gotten some type of
error. I could use the dsum formula but I would like to stick to"if"
formulas. Any suggestions?

Col. A Col. B Col.C
1 123 XD
5 222 XD
10 123 CC
10 123 XD

Something like this should work fine:
=SUMPRODUCT((B1:B4=123)*(C1:C4="XD"),A1:A4)
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"Rody2003" wrote:
I need to sum a column based on two different variables. For example I need
to sum Column A if Column B = 123 and Column C = XD. For the example below
the sumif formula would equal "11." I have tried creating this formula
several times but I have yet to succeed as I have always gotten some type of
error. I could use the dsum formula but I would like to stick to"if"
formulas. Any suggestions?

Col. A Col. B Col.C
1 123 XD
5 222 XD
10 123 CC
10 123 XD

Thanks alot the formula is working perfectly!

"Max" wrote:

Something like this should work fine:
=SUMPRODUCT((B1:B4=123)*(C1:C4="XD"),A1:A4)
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"Rody2003" wrote:
I need to sum a column based on two different variables. For example I need
to sum Column A if Column B = 123 and Column C = XD. For the example below
the sumif formula would equal "11." I have tried creating this formula
several times but I have yet to succeed as I have always gotten some type of
error. I could use the dsum formula but I would like to stick to"if"
formulas. Any suggestions?

Col. A Col. B Col.C
1 123 XD
5 222 XD
10 123 CC
10 123 XD

How do I mark this as answered?

"John C" wrote:

SUMPRODUCT is the way to go here.

=SUMPRODUCT(--($B$2:$B$100=123),--($C$2:$C$100="XD"),($A$2:$A$100))

Note, I am assuming all values in column A and column B are numbers, not text.
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Rody2003" wrote:

I need to sum a column based on two different variables. For example I need
to sum Column A if Column B = 123 and Column C = XD. For the example below
the sumif formula would equal "11." I have tried creating this formula
several times but I have yet to succeed as I have always gotten some type of
error. I could use the dsum formula but I would like to stick to"if"
formulas. Any suggestions?

Col. A Col. B Col.C
1 123 XD
5 222 XD
10 123 CC
10 123 XD

How do I mark this as answered?

From where you're posting / reading responses, ie in MS' webpages
just click the "Yes" button (like the one below) in John's response

Do note that in John's response, the parens around: $A$2:$A$100
is not necessary, ie it could be just:

=SUMPRODUCT(--($B$2:$B$100=123),--($C$2:$C$100="XD"),$A$2:$A$100)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---

You're welcome. Good to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---
"Rody2003" wrote in message
...
Thanks a lot the formula is working perfectly!