SUM question

[Nick Ng]

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

 

[Mike H]

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

 

[barry houdini]

To get reults in the "0C" column so that you can check try this formul
in D1 copied down

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

extend ranges as require

 

[Nick Ng]

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

 

[barry houdini]

Hello Nick,

Did you see my suggestion? it would have given you the results in th
0C rows only so that you can make a comparison directly in tha
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),""

 

[ryguy7272]

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/excel-pivot-table-tutorial-wizard-usage-and-basic-manipulation/

HTH,
Ryan--

 

[Ashish Mathur]

Hi,

You may use this formula in D3 and copy down. I have assumed that the data
is in range A3:C14. In A22, 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