Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM question
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM question
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM question
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM question
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM question
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM question
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Question... | Excel Discussion (Misc queries) | |||
where can I see my question and answer? Yesterday I ask a question | Excel Discussion (Misc queries) | |||
question about vba | Excel Discussion (Misc queries) | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions |