Hi, I was wondering if someone could help me out with a SUM question.

A B C D
0C 12345 40
1C 12345 10
1C 12345 10
1C 12345 10
1C 12345 10
0C 23456 20
1C 23456 10
1C 23456 10
0C 89012 25
1C 89012 10
1C 89012 10
1C 89012 10

A brief explanation about the data above: Column C when Column A=0C is
supposed to be the sum of all AND(Column B with similar values, Column A=1C).
However, that is sometimes not the case, as you can see for record 89012 (the
sum should be 30, not 25).

So, what I'm trying to do in Column D is to manually sum up Column A=1C when
Column B=B, then compare it to the corresponding Column A=0C to see if
they're the same.

In Excel terms, in Column D, how do I perform a SUM function with the
following criteria:
Column A=1C -AND-
Column B = similar value

I hope I was clear enough. Sorry if its confusing, I'm not much good with
programming. Thanks in advance!

-Nick

Nick,

I got confused reading your question. The nearest I got to what I think you
want is this

=SUMPRODUCT((A1:A12="1C")*(B1:B12=89012)*(C1:C12))

If that's wrong re-post your question with the answers manually entered for
column D

Mike

"Nick Ng" wrote:

Hi, I was wondering if someone could help me out with a SUM question.

A B C D
0C 12345 40
1C 12345 10
1C 12345 10
1C 12345 10
1C 12345 10
0C 23456 20
1C 23456 10
1C 23456 10
0C 89012 25
1C 89012 10
1C 89012 10
1C 89012 10

A brief explanation about the data above: Column C when Column A=0C is
supposed to be the sum of all AND(Column B with similar values, Column A=1C).
However, that is sometimes not the case, as you can see for record 89012 (the
sum should be 30, not 25).

So, what I'm trying to do in Column D is to manually sum up Column A=1C when
Column B=B, then compare it to the corresponding Column A=0C to see if
they're the same.

In Excel terms, in Column D, how do I perform a SUM function with the
following criteria:
Column A=1C -AND-
Column B = similar value

I hope I was clear enough. Sorry if its confusing, I'm not much good with
programming. Thanks in advance!

-Nick

Let me rephrase the question:

A.......... B........ C.......D
0Y.....12345.....40.....
1Y.....12345.....10.....40
1Y.....12345.....10.....40
1Y.....12345.....10.....40
1Y.....12345.....10.....40
0Y.....23456.....20.....
1Y.....23456.....10.....20
1Y.....23456.....10.....20
0Y.....89012.....25.....
1Y.....89012.....10.....30
1Y.....89012.....10.....30
1Y.....89012.....10.....30
etc

I've manually entered the results I'm looking for in Column D. Basically,
its the sum of Column C, obeying the following 2 criterias:

Criteria 1: Column A = 1Y
Criteria 2: all similar values of Column B

Take 89012 in Column B for example. The result in Column D would be 30,
because there are 3 occurences of 89012 with 1Y in Column A, and each
occurrence has a value of 10 in Column C. Hence, the sum = 30.

I hope the above is a bit clearer. Thanks for the assistance!

-Nick


"Mike H" wrote:

Nick,

I got confused reading your question. The nearest I got to what I think you
want is this

=SUMPRODUCT((A1:A12="1C")*(B1:B12=89012)*(C1:C12))

If that's wrong re-post your question with the answers manually entered for
column D

Mike

"Nick Ng" wrote:

Hi, I was wondering if someone could help me out with a SUM question.

A B C D
0C 12345 40
1C 12345 10
1C 12345 10
1C 12345 10
1C 12345 10
0C 23456 20
1C 23456 10
1C 23456 10
0C 89012 25
1C 89012 10
1C 89012 10
1C 89012 10

A brief explanation about the data above: Column C when Column A=0C is
supposed to be the sum of all AND(Column B with similar values, Column A=1C).
However, that is sometimes not the case, as you can see for record 89012 (the
sum should be 30, not 25).

So, what I'm trying to do in Column D is to manually sum up Column A=1C when
Column B=B, then compare it to the corresponding Column A=0C to see if
they're the same.

In Excel terms, in Column D, how do I perform a SUM function with the
following criteria:
Column A=1C -AND-
Column B = similar value

I hope I was clear enough. Sorry if its confusing, I'm not much good with
programming. Thanks in advance!

-Nick

Hello Nick,

Did you see my suggestion? it would have given you the results in the
0C rows only so that you can make a comparison directly in that
row....but you can easily change it to match your requirement, i.e.

=IF(A1="1C",SUMPRODUCT((A$1:A$100="1C")*(B$1:B$100 =B1),C$1:C$100),"")


--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145249

I think this is what you want:
http://blogs.techrepublic.com.com/msoffice/?p=416

You may want to consider using a Pivot Table too (or as an alternative):
http://peltiertech.com/Excel/Pivots/pivottables.htm
http://www.babeled.com/2008/07/18/ex...-manipulation/

HTH,
Ryan--
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Nick Ng" wrote:

Let me rephrase the question:

A.......... B........ C.......D
0Y.....12345.....40.....
1Y.....12345.....10.....40
1Y.....12345.....10.....40
1Y.....12345.....10.....40
1Y.....12345.....10.....40
0Y.....23456.....20.....
1Y.....23456.....10.....20
1Y.....23456.....10.....20
0Y.....89012.....25.....
1Y.....89012.....10.....30
1Y.....89012.....10.....30
1Y.....89012.....10.....30
etc

I've manually entered the results I'm looking for in Column D. Basically,
its the sum of Column C, obeying the following 2 criterias:

Criteria 1: Column A = 1Y
Criteria 2: all similar values of Column B

Take 89012 in Column B for example. The result in Column D would be 30,
because there are 3 occurences of 89012 with 1Y in Column A, and each
occurrence has a value of 10 in Column C. Hence, the sum = 30.

I hope the above is a bit clearer. Thanks for the assistance!

-Nick


"Mike H" wrote:

Nick,

I got confused reading your question. The nearest I got to what I think you
want is this

=SUMPRODUCT((A1:A12="1C")*(B1:B12=89012)*(C1:C12))

If that's wrong re-post your question with the answers manually entered for
column D

Mike

"Nick Ng" wrote:

Hi, I was wondering if someone could help me out with a SUM question.

A B C D
0C 12345 40
1C 12345 10
1C 12345 10
1C 12345 10
1C 12345 10
0C 23456 20
1C 23456 10
1C 23456 10
0C 89012 25
1C 89012 10
1C 89012 10
1C 89012 10

A brief explanation about the data above: Column C when Column A=0C is
supposed to be the sum of all AND(Column B with similar values, Column A=1C).
However, that is sometimes not the case, as you can see for record 89012 (the
sum should be 30, not 25).

So, what I'm trying to do in Column D is to manually sum up Column A=1C when
Column B=B, then compare it to the corresponding Column A=0C to see if
they're the same.

In Excel terms, in Column D, how do I perform a SUM function with the
following criteria:
Column A=1C -AND-
Column B = similar value

I hope I was clear enough. Sorry if its confusing, I'm not much good with
programming. Thanks in advance!

-Nick

Hi,

You may use this formula in D3 and copy down. I have assumed that the data
is in range A3:C14. In A2:D2, enter Name, code, Nos and Nos

=IF(A3="0Y","",DSUM($A$2:$C$14,D$2,$A$2:$B3)-SUMPRODUCT(((($A2:A$3="1Y")*($B2:B$3<B3))+($A2:A$ 3="0Y"))*($C2:C$3)))

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Nick Ng" wrote in message
...
Let me rephrase the question:

A.......... B........ C.......D
0Y.....12345.....40.....
1Y.....12345.....10.....40
1Y.....12345.....10.....40
1Y.....12345.....10.....40
1Y.....12345.....10.....40
0Y.....23456.....20.....
1Y.....23456.....10.....20
1Y.....23456.....10.....20
0Y.....89012.....25.....
1Y.....89012.....10.....30
1Y.....89012.....10.....30
1Y.....89012.....10.....30
etc

I've manually entered the results I'm looking for in Column D. Basically,
its the sum of Column C, obeying the following 2 criterias:

Criteria 1: Column A = 1Y
Criteria 2: all similar values of Column B

Take 89012 in Column B for example. The result in Column D would be 30,
because there are 3 occurences of 89012 with 1Y in Column A, and each
occurrence has a value of 10 in Column C. Hence, the sum = 30.

I hope the above is a bit clearer. Thanks for the assistance!

-Nick


"Mike H" wrote:

Nick,

I got confused reading your question. The nearest I got to what I think
you
want is this

=SUMPRODUCT((A1:A12="1C")*(B1:B12=89012)*(C1:C12))

If that's wrong re-post your question with the answers manually entered
for
column D

Mike

"Nick Ng" wrote:

Hi, I was wondering if someone could help me out with a SUM question.

A B C D
0C 12345 40
1C 12345 10
1C 12345 10
1C 12345 10
1C 12345 10
0C 23456 20
1C 23456 10
1C 23456 10
0C 89012 25
1C 89012 10
1C 89012 10
1C 89012 10

A brief explanation about the data above: Column C when Column A=0C is
supposed to be the sum of all AND(Column B with similar values, Column
A=1C).
However, that is sometimes not the case, as you can see for record
89012 (the
sum should be 30, not 25).

So, what I'm trying to do in Column D is to manually sum up Column A=1C
when
Column B=B, then compare it to the corresponding Column A=0C to see if
they're the same.

In Excel terms, in Column D, how do I perform a SUM function with the
following criteria:
Column A=1C -AND-
Column B = similar value

I hope I was clear enough. Sorry if its confusing, I'm not much good
with
programming. Thanks in advance!

-Nick