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

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

Post by TWlue » Fri, 15 Dec 2006 01:04:02


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)
 
 
 

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

Post by RagDye » Fri, 15 Dec 2006 01:13:17

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)*(D1:D100=E2)*C1:C100)

To add other criteria, simply add another argument:

=Sumproduct((A1:A100=E1)*(D1:D100=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!
=====================================================



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)

 
 
 

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

Post by RagDye » Fri, 15 Dec 2006 02:06:53

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 !
-----------------------------------------------------------------------------------------------



=SUMPRODUCT((Xewks!A1:A425=A10)*(Xewks!E1:E425=5)*(Xewks!D1:D425="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!
 
 
 

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

Post by RagDye » Fri, 15 Dec 2006 05:14:30

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

RD

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

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

Post by U2Vi » Sat, 10 Oct 2009 13:31:01

Hi,

This formula works great whenever there are values in the columns, but when
the columns contain formulas I get #N/A. Do you know how to go about this?

Seb
 
 
 

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

Post by T. Valk » Sat, 10 Oct 2009 13:42:38

>when the columns contain formulas I get #N/A.

Post the *exact* formula you are trying to use that returns #N/A.

--
Biff
Microsoft Excel MVP
 
 
 

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

Post by U2Vi » Sat, 10 Oct 2009 14:32:01

This is the formula that returns #N/A:
=SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New
Code'!$AI$2:$AI$7176=B10),'New Code'!$AM$2:$AM$7176)) and I enter it as an
array formula

This one, for example, works:
=SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New
Code'!$AF$2:$AF$7176=F7),'New Code'!$AM$2:$AM$7176)) also entered as an array
formula

Seb
 
 
 

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

Post by T. Valk » Sat, 10 Oct 2009 15:00:52

The only thing that's different in those formulas other than the criteria
cells is the range AI2:AI7176.

Are there any #N/A errors already in that range? If so, can you fix the
formulas so they don't return those errors? If you can't or don't want to
fix those, what type of data is in that range, text, numbers, both?

--
Biff
Microsoft Excel MVP
 
 
 

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

Post by U2Vi » Sat, 10 Oct 2009 16:00:01

Thank you so much. I did have one #N/A in the range.
 
 
 

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

Post by T. Valk » Sun, 11 Oct 2009 00:21:07

ood deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Seb" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

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

Post by dHJhdmNvZT » Mon, 09 Nov 2009 02:40:02

i, Biff --

I just stumbled across this thread, and made use of the formulation for
conditional summing with complex criteria. Thanks a bunch!

Question for you: the formula looks like, and behaves like, there is an
implied conversion from Boolean values of TRUE/FALSE to numerical values of
1/0 going on. That is, if all conditions are true, multiply the sum column
value by 1. If not all of the conditions are true, multiply the sum column
value by 0. IOW, a logical AND of multiple conditions. Is that a good
description? I tried what I thought was the equivalent:

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

. . . but that didn't work, I assume because of the logic of the array
formula syntax -- correct?

I also discovered a further subtlety of this formulation. What I actually
needed to do logically was:

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

The syntax that gave the correct result was to add two separate conditional
summations together, i.e.:

{SUM(cond1*cond2*sumcolumnvalue)+SUM(cond1*cond3*sumcolumnvalue)}

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.

Anywho, just wanted to share this discovery and help out anyone else who is
trying to do something similar.

"T. Valko" wrote:

 
 
 

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

Post by dHJhdmNvZT » Mon, 09 Nov 2009 02:51:01

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

Jeff
 
 
 

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

Post by T. Valk » Mon, 09 Nov 2009 04:23:09

>Is that a good description?

Yes


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")+(D1:D10="y")>0),C1:C10)

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

--
Biff
Microsoft Excel MVP
 
 
 

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

Post by VGVycn » Tue, 27 Apr 2010 02:37:01

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".???
 
 
 

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

Post by T. Valk » Tue, 27 Apr 2010 02:51:42

To exclude negative values from the sum...

Works in any "modern" version of Excel:

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

If you're using Excel 2007:

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

--
Biff
Microsoft Excel MVP