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)

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)

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)

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!

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!

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

--

Regards,

RD

---------------------------------------------------------------------------

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

---------------------------------------------------------------------------

--

Regards,

RD

---------------------------------------------------------------------------

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

---------------------------------------------------------------------------

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

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

>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

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

--

Biff

Microsoft Excel MVP

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

=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

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

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

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

ood deal. Thanks for the feedback!

--

Biff

Microsoft Excel MVP

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

news: XXXX@XXXXX.COM ...

--

Biff

Microsoft Excel MVP

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

news: XXXX@XXXXX.COM ...

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:

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:

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

Jeff

Jeff

>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

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

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

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

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

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

1. Counting A's in a table column 1, F's in column 2, etc...

2. "Matching" column A's values in column B

3. Tabbing from column to column to column in a 5 column document.

4. View only items in the left column that have X's in right columns

5. How to add previous sums in a column to current sums in a column?

6. summing number of 'y's in a filtered column

7. Select Sum(table1.column), Sum(table2.column)

8. best way to sum a column with #N/a's?

9. Chart with One Column Column and Two Stacked Columns

10. Referencing date column A & time column B to get info from column

11. select range in column, calculate on range plus 3 columns, output in range plus 7 columns

12. Columns, columns, too many columns

13. Combine multiple columns into two long columns, Repeating rows in first column

14. what formula do i put for column m = column k minus column l i

15. what formula do i put for column m = column k minus column l in e.