A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Can I SUM column C IF Column A=X AND Column D=Y?



 
 
Thread Tools Display Modes
  #1  
Old December 13th, 2006, 05:04 PM posted to microsoft.public.excel.worksheet.functions
Minx
external usenet poster
 
Posts: 14
Default Can I SUM column C IF Column A=X AND Column D=Y?

If you could follow that...
I have a number of columns of information for any given row. I would like to
set up a sheet that will sum Column C for all rows IF Column A = {a given
value} AND Column D = {a different given value}. I may even need to throw in
that Column B = {another given value}. Can I do this and how?

I've run across something about VLOOKUP, but I'm not sure how to make that
work. I'm primarily a CAD operator making brief forays into Excel, and I keep
thinking it has got to be easier than all the manual sorting I'm currently
doing.

I'm using Excel 2003 (on Windows XP SP2)
  #2  
Old December 13th, 2006, 05:13 PM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,482
Default Can I SUM column C IF Column A=X AND Column D=Y?

Assign your "given values" to particular cells, so they can easily be
changed, without having to revise the formula itself (E1, E2).


=Sumproduct((A1:A100=E1)*(D1100=E2)*C1:C100)

To add other criteria, simply add another argument:

=Sumproduct((A1:A100=E1)*(D1100=E2)*(B1:B100=E3) *C1:C100)

All ranges should be the same size, and you *cannot* use entire columns
(A:A - B:B)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Minx" wrote in message
...
If you could follow that...
I have a number of columns of information for any given row. I would like to
set up a sheet that will sum Column C for all rows IF Column A = {a given
value} AND Column D = {a different given value}. I may even need to throw in
that Column B = {another given value}. Can I do this and how?

I've run across something about VLOOKUP, but I'm not sure how to make that
work. I'm primarily a CAD operator making brief forays into Excel, and I
keep
thinking it has got to be easier than all the manual sorting I'm currently
doing.

I'm using Excel 2003 (on Windows XP SP2)


  #3  
Old December 13th, 2006, 05:42 PM posted to microsoft.public.excel.worksheet.functions
Minx
external usenet poster
 
Posts: 14
Default Can I SUM column C IF Column A=X AND Column D=Y?

=SUMPRODUCT((Xewks!A1:A425=A10)*(Xewks!E1:E425=5)* (Xewks!D1425="B
Borrow")*Xewks!B1:B425)

Here is what I put in for my SUMPRODUCT formula, but it just gives me a
#VALUE error. Closer than what I was doing before, but still not working - so
I must be misunderstanding something.

Thank you for your help!

"RagDyeR" wrote:

Assign your "given values" to particular cells, so they can easily be
changed, without having to revise the formula itself (E1, E2).


=Sumproduct((A1:A100=E1)*(D1100=E2)*C1:C100)

To add other criteria, simply add another argument:

=Sumproduct((A1:A100=E1)*(D1100=E2)*(B1:B100=E3) *C1:C100)

All ranges should be the same size, and you *cannot* use entire columns
(A:A - B:B)


  #4  
Old December 13th, 2006, 06:06 PM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,482
Default Can I SUM column C IF Column A=X AND Column D=Y?

You probably have values in B1 to B425 that are *not* true XL recognized
numbers!

Could B1 be a text header?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Minx" wrote in message
...
=SUMPRODUCT((Xewks!A1:A425=A10)*(Xewks!E1:E425=5)* (Xewks!D1425="B
Borrow")*Xewks!B1:B425)

Here is what I put in for my SUMPRODUCT formula, but it just gives me a
#VALUE error. Closer than what I was doing before, but still not working -
so
I must be misunderstanding something.

Thank you for your help!

"RagDyeR" wrote:

Assign your "given values" to particular cells, so they can easily be
changed, without having to revise the formula itself (E1, E2).


=Sumproduct((A1:A100=E1)*(D1100=E2)*C1:C100)

To add other criteria, simply add another argument:

=Sumproduct((A1:A100=E1)*(D1100=E2)*(B1:B100=E3) *C1:C100)

All ranges should be the same size, and you *cannot* use entire columns
(A:A - B:B)



  #5  
Old December 13th, 2006, 06:33 PM posted to microsoft.public.excel.worksheet.functions
Minx
external usenet poster
 
Posts: 14
Default Can I SUM column C IF Column A=X AND Column D=Y?

That was it!
(Actually, everything in Row 1 is a text header)
Thank you so much!!


"RagDyeR" wrote:

You probably have values in B1 to B425 that are *not* true XL recognized
numbers!

Could B1 be a text header?


  #6  
Old December 13th, 2006, 09:14 PM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,482
Default Can I SUM column C IF Column A=X AND Column D=Y?

You're welcome, and thank you for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Minx" wrote in message
...
That was it!
(Actually, everything in Row 1 is a text header)
Thank you so much!!


"RagDyeR" wrote:

You probably have values in B1 to B425 that are *not* true XL recognized
numbers!

Could B1 be a text header?



  #7  
Old November 7th, 2009, 06:51 PM posted to microsoft.public.excel.worksheet.functions
travcoe21
external usenet poster
 
Posts: 3
Default Can I SUM column C IF Column A=X AND Column D=Y?

Sorry, I meant cond2 and cond3 are never both true for the same sumcolumnvalue.

Jeff

"travcoe21" wrote:
Note that in order for this to work the way it's supposed to, cond1 and
cond3 must never both be true for the same sumcolumnvalue. Which for my
application is always the case.

  #8  
Old November 7th, 2009, 08:23 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Can I SUM column C IF Column A=X AND Column D=Y?

Is that a good description?

Yes

{SUM(AND(cond1, cond2)*sumcolumnvalue)}


AND returns a single result where you need an array of results.

It sounds like you want an OR comparison: sum C if A = x *or* D = y.

Try one of these:

=SUMPRODUCT(--((A1:A10="x")+(D110="y")0),C1:C10)

=SUMPRODUCT(SIGN((A1:A10="x")+(D110="y")),C1:C10 )

--
Biff
Microsoft Excel MVP


"travcoe21" wrote in message
...
Sorry, I meant cond2 and cond3 are never both true for the same
sumcolumnvalue.

Jeff

"travcoe21" wrote:
Note that in order for this to work the way it's supposed to, cond1 and
cond3 must never both be true for the same sumcolumnvalue. Which for my
application is always the case.



  #9  
Old April 25th, 2010, 06:37 PM posted to microsoft.public.excel.worksheet.functions
terry
external usenet poster
 
Posts: 622
Default Can I SUM column C IF Column A=X AND Column D=Y?

I am trying to sum a column of both negative and positive numbers on another
worksheet based on two conditions. I have tried to construct an array that
would =Sum(if((A2:A278,="2000")*(M2:M278,="0"),M2:M278) )

Should that not sum the column M2:M278 positive integers based on the
criteria that all rows in A2:A278 are equal to "2000".???



"Minx" wrote:

If you could follow that...
I have a number of columns of information for any given row. I would like to
set up a sheet that will sum Column C for all rows IF Column A = {a given
value} AND Column D = {a different given value}. I may even need to throw in
that Column B = {another given value}. Can I do this and how?

I've run across something about VLOOKUP, but I'm not sure how to make that
work. I'm primarily a CAD operator making brief forays into Excel, and I keep
thinking it has got to be easier than all the manual sorting I'm currently
doing.

I'm using Excel 2003 (on Windows XP SP2)

  #10  
Old April 25th, 2010, 06:51 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Can I SUM column C IF Column A=X AND Column D=Y?

To exclude negative values from the sum...

Works in any "modern" version of Excel:

=SUMPRODUCT(--(A2:A278=2000),--(M2:M2780),M2:M278)

If you're using Excel 2007:

=SUMIFS(M2:M278,A2:A278,2000,M2:M278,"0")

--
Biff
Microsoft Excel MVP


"Terry" wrote in message
...
I am trying to sum a column of both negative and positive numbers on
another
worksheet based on two conditions. I have tried to construct an array that
would =Sum(if((A2:A278,="2000")*(M2:M278,="0"),M2:M278) )

Should that not sum the column M2:M278 positive integers based on the
criteria that all rows in A2:A278 are equal to "2000".???



"Minx" wrote:

If you could follow that...
I have a number of columns of information for any given row. I would like
to
set up a sheet that will sum Column C for all rows IF Column A = {a given
value} AND Column D = {a different given value}. I may even need to throw
in
that Column B = {another given value}. Can I do this and how?

I've run across something about VLOOKUP, but I'm not sure how to make
that
work. I'm primarily a CAD operator making brief forays into Excel, and I
keep
thinking it has got to be easier than all the manual sorting I'm
currently
doing.

I'm using Excel 2003 (on Windows XP SP2)



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.