Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for SUM with duel criteria
Greetings,
I need to write the code for the sum with criteria of two different columns. Say for example, Col A has Date, Col B has item no and col C has a value to be summed up. I have an input box for the date and item no. What I need to do is to lookup for date less the date of input, item no equall to item no of input in the same row and if both are matching then sum the value. I can do it for one criteria but not for the two criteria. Request help please. Regards, Shetty. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for SUM with duel criteria
set rng = Range(Cells(1,3),Cells(rows.count,3).end(xlup))
for each cell in rng if cell.offset(0,-2) < myDate and cell.Offset(0,-1).value = myItemNo then if isnumeric(cell) then mytotal = myTotal + cell.Value End if end if Next if a worksheet formula =Sumproduct(-(A1:A100<F1),-(B1:B100=G1),C1:C100) F1 contains the date G1 contains the item number -- Regards, Tom Ogilvy "Shetty" wrote in message om... Greetings, I need to write the code for the sum with criteria of two different columns. Say for example, Col A has Date, Col B has item no and col C has a value to be summed up. I have an input box for the date and item no. What I need to do is to lookup for date less the date of input, item no equall to item no of input in the same row and if both are matching then sum the value. I can do it for one criteria but not for the two criteria. Request help please. Regards, Shetty. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for SUM with duel criteria
Hi Tom,
This will be very much usefull to me. But this may not work as required. When the code runs, active sheet may be different then this sheet(named master, where all the three columns are). Also I have to each and every rows and not every third row. This is where I could not understand the code statement . set rng = Range(Cells(1,3),Cells(rows.count,3).end(xlup)) There are other things as well which are confusing. Say, You have set a fixed range where as my data is going to be updated every 30mins with about 50-60 rows(no of rows added are not fixed). So how to update range with latest range? Can you please elaborate and explaine in more detail? Regards, Tom Ogilvy wrote in message ... set rng = Range(Cells(1,3),Cells(rows.count,3).end(xlup)) for each cell in rng if cell.offset(0,-2) < myDate and cell.Offset(0,-1).value = myItemNo then if isnumeric(cell) then mytotal = myTotal + cell.Value End if end if Next if a worksheet formula =Sumproduct(-(A1:A100<F1),-(B1:B100=G1),C1:C100) F1 contains the date G1 contains the item number -- Regards, Tom Ogilvy "Shetty" wrote in message om... Greetings, I need to write the code for the sum with criteria of two different columns. Say for example, Col A has Date, Col B has item no and col C has a value to be summed up. I have an input box for the date and item no. What I need to do is to lookup for date less the date of input, item no equall to item no of input in the same row and if both are matching then sum the value. I can do it for one criteria but not for the two criteria. Request help please. Regards, Shetty. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for SUM with duel criteria
I didn't say anything about a fixed range - the End(xlup) finds the bottom
of the data in Column 3. The 3 refers to column C, not to stepping through rows 3 at a time - it steps through every row from the first to the last row in column C that has a value. To refer to sheet named "master": Dim rng as Range, cell as Range Dim myTotal as Double With Worksheets("Master") set rng = .Range(.Cells(1,3),.Cells(rows.count,3).end(xlup)) End With for each cell in rng if cell.offset(0,-2) < myDate and cell.Offset(0,-1).value = myItemNo then if isnumeric(cell) then mytotal = myTotal + cell.Value End if end if Next -- Regards, Tom Ogilvy "Malcom" wrote in message ... Hi Tom, This will be very much usefull to me. But this may not work as required. When the code runs, active sheet may be different then this sheet(named master, where all the three columns are). Also I have to each and every rows and not every third row. This is where I could not understand the code statement . set rng = Range(Cells(1,3),Cells(rows.count,3).end(xlup)) There are other things as well which are confusing. Say, You have set a fixed range where as my data is going to be updated every 30mins with about 50-60 rows(no of rows added are not fixed). So how to update range with latest range? Can you please elaborate and explaine in more detail? Regards, Tom Ogilvy wrote in message ... set rng = Range(Cells(1,3),Cells(rows.count,3).end(xlup)) for each cell in rng if cell.offset(0,-2) < myDate and cell.Offset(0,-1).value = myItemNo then if isnumeric(cell) then mytotal = myTotal + cell.Value End if end if Next if a worksheet formula =Sumproduct(-(A1:A100<F1),-(B1:B100=G1),C1:C100) F1 contains the date G1 contains the item number -- Regards, Tom Ogilvy "Shetty" wrote in message om... Greetings, I need to write the code for the sum with criteria of two different columns. Say for example, Col A has Date, Col B has item no and col C has a value to be summed up. I have an input box for the date and item no. What I need to do is to lookup for date less the date of input, item no equall to item no of input in the same row and if both are matching then sum the value. I can do it for one criteria but not for the two criteria. Request help please. Regards, Shetty. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for SUM with duel criteria
Tom,
Please dont take it personnally. I was not critisising you. It was just an effort to express my understanding about the code you have written for Shetty. I am sorry to intrude in between. In fect I have seen you replying querrys of others and helping them out in their problems and so I thought somebody can help me also on how to modify your code for Shetty to my requirement. Since I am bigainer in this area, I could not understand range and 3. Once again I am sorry to hurt you (Unintensionally). Regards, Tom Ogilvy wrote in message ... I didn't say anything about a fixed range - the End(xlup) finds the bottom of the data in Column 3. The 3 refers to column C, not to stepping through rows 3 at a time - it steps through every row from the first to the last row in column C that has a value. To refer to sheet named "master": Dim rng as Range, cell as Range Dim myTotal as Double With Worksheets("Master") set rng = .Range(.Cells(1,3),.Cells(rows.count,3).end(xlup)) End With for each cell in rng if cell.offset(0,-2) < myDate and cell.Offset(0,-1).value = myItemNo then if isnumeric(cell) then mytotal = myTotal + cell.Value End if end if Next -- Regards, Tom Ogilvy "Malcom" wrote in message ... Hi Tom, This will be very much usefull to me. But this may not work as required. When the code runs, active sheet may be different then this sheet(named master, where all the three columns are). Also I have to each and every rows and not every third row. This is where I could not understand the code statement . set rng = Range(Cells(1,3),Cells(rows.count,3).end(xlup)) There are other things as well which are confusing. Say, You have set a fixed range where as my data is going to be updated every 30mins with about 50-60 rows(no of rows added are not fixed). So how to update range with latest range? Can you please elaborate and explaine in more detail? Regards, Tom Ogilvy wrote in message ... set rng = Range(Cells(1,3),Cells(rows.count,3).end(xlup)) for each cell in rng if cell.offset(0,-2) < myDate and cell.Offset(0,-1).value = myItemNo then if isnumeric(cell) then mytotal = myTotal + cell.Value End if end if Next if a worksheet formula =Sumproduct(-(A1:A100<F1),-(B1:B100=G1),C1:C100) F1 contains the date G1 contains the item number -- Regards, Tom Ogilvy "Shetty" wrote in message om... Greetings, I need to write the code for the sum with criteria of two different columns. Say for example, Col A has Date, Col B has item no and col C has a value to be summed up. I have an input box for the date and item no. What I need to do is to lookup for date less the date of input, item no equall to item no of input in the same row and if both are matching then sum the value. I can do it for one criteria but not for the two criteria. Request help please. Regards, Shetty. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for SUM with duel criteria
Hi Tom,
Its great. My problem is solved. However, just for acadamic intreste, is it possible to have more criteria then just two? Thanks and Regards, Shetty Tom Ogilvy wrote in message ... set rng = Range(Cells(1,3),Cells(rows.count,3).end(xlup)) for each cell in rng if cell.offset(0,-2) < myDate and cell.Offset(0,-1).value = myItemNo then if isnumeric(cell) then mytotal = myTotal + cell.Value End if end if Next if a worksheet formula =Sumproduct(-(A1:A100<F1),-(B1:B100=G1),C1:C100) F1 contains the date G1 contains the item number -- Regards, Tom Ogilvy "Shetty" wrote in message om... Greetings, I need to write the code for the sum with criteria of two different columns. Say for example, Col A has Date, Col B has item no and col C has a value to be summed up. I have an input box for the date and item no. What I need to do is to lookup for date less the date of input, item no equall to item no of input in the same row and if both are matching then sum the value. I can do it for one criteria but not for the two criteria. Request help please. Regards, Shetty. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for SUM with duel criteria
Tom,
Please dont take it personnally. I was not critisising you. It was just an effort to express my understanding about the code you have written for Shetty. I am sorry to intrude in between. In fect I have seen you replying querrys of others and helping them out in their problems and so I thought somebody can help me also on how to modify your code for Shetty to my requirement. Since I am bigainer in this area, I could not understand range and 3. Once again I am sorry to hurt you (Unintensionally). Regards, Tom Ogilvy wrote in message ... I didn't say anything about a fixed range - the End(xlup) finds the bottom of the data in Column 3. The 3 refers to column C, not to stepping through rows 3 at a time - it steps through every row from the first to the last row in column C that has a value. To refer to sheet named "master": Dim rng as Range, cell as Range Dim myTotal as Double With Worksheets("Master") set rng = .Range(.Cells(1,3),.Cells(rows.count,3).end(xlup)) End With for each cell in rng if cell.offset(0,-2) < myDate and cell.Offset(0,-1).value = myItemNo then if isnumeric(cell) then mytotal = myTotal + cell.Value End if end if Next -- Regards, Tom Ogilvy "Malcom" wrote in message ... Hi Tom, This will be very much usefull to me. But this may not work as required. When the code runs, active sheet may be different then this sheet(named master, where all the three columns are). Also I have to each and every rows and not every third row. This is where I could not understand the code statement . set rng = Range(Cells(1,3),Cells(rows.count,3).end(xlup)) There are other things as well which are confusing. Say, You have set a fixed range where as my data is going to be updated every 30mins with about 50-60 rows(no of rows added are not fixed). So how to update range with latest range? Can you please elaborate and explaine in more detail? Regards, Tom Ogilvy wrote in message ... set rng = Range(Cells(1,3),Cells(rows.count,3).end(xlup)) for each cell in rng if cell.offset(0,-2) < myDate and cell.Offset(0,-1).value = myItemNo then if isnumeric(cell) then mytotal = myTotal + cell.Value End if end if Next if a worksheet formula =Sumproduct(-(A1:A100<F1),-(B1:B100=G1),C1:C100) F1 contains the date G1 contains the item number -- Regards, Tom Ogilvy "Shetty" wrote in message om... Greetings, I need to write the code for the sum with criteria of two different columns. Say for example, Col A has Date, Col B has item no and col C has a value to be summed up. I have an input box for the date and item no. What I need to do is to lookup for date less the date of input, item no equall to item no of input in the same row and if both are matching then sum the value. I can do it for one criteria but not for the two criteria. Request help please. Regards, Shetty. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for SUM with duel criteria
For both the code and the formula, yes.
in the formula, if you have an odd number of conditions you need two negation operators on at least one of the conditions. =Sumproduct(-(A1:A100<F1),-(B1:B100=G1),--(C1:C100=H1),D1:D100) in the code, just connect the conditions with And. -- Regards, Tom Ogilvy "shetty" wrote in message ... Hi Tom, Its great. My problem is solved. However, just for acadamic intreste, is it possible to have more criteria then just two? Thanks and Regards, Shetty Tom Ogilvy wrote in message ... set rng = Range(Cells(1,3),Cells(rows.count,3).end(xlup)) for each cell in rng if cell.offset(0,-2) < myDate and cell.Offset(0,-1).value = myItemNo then if isnumeric(cell) then mytotal = myTotal + cell.Value End if end if Next if a worksheet formula =Sumproduct(-(A1:A100<F1),-(B1:B100=G1),C1:C100) F1 contains the date G1 contains the item number -- Regards, Tom Ogilvy "Shetty" wrote in message om... Greetings, I need to write the code for the sum with criteria of two different columns. Say for example, Col A has Date, Col B has item no and col C has a value to be summed up. I have an input box for the date and item no. What I need to do is to lookup for date less the date of input, item no equall to item no of input in the same row and if both are matching then sum the value. I can do it for one criteria but not for the two criteria. Request help please. Regards, Shetty. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for SUM with duel criteria
Not sure why you think I was reacting badly. I just answered your
questions and clarified that I hadn't stated anything about a fixed range. -- Regards, Tom Ogilvy "Malcom" wrote in message ... Tom, Please dont take it personnally. I was not critisising you. It was just an effort to express my understanding about the code you have written for Shetty. I am sorry to intrude in between. In fect I have seen you replying querrys of others and helping them out in their problems and so I thought somebody can help me also on how to modify your code for Shetty to my requirement. Since I am bigainer in this area, I could not understand range and 3. Once again I am sorry to hurt you (Unintensionally). Regards, Tom Ogilvy wrote in message ... I didn't say anything about a fixed range - the End(xlup) finds the bottom of the data in Column 3. The 3 refers to column C, not to stepping through rows 3 at a time - it steps through every row from the first to the last row in column C that has a value. To refer to sheet named "master": Dim rng as Range, cell as Range Dim myTotal as Double With Worksheets("Master") set rng = .Range(.Cells(1,3),.Cells(rows.count,3).end(xlup)) End With for each cell in rng if cell.offset(0,-2) < myDate and cell.Offset(0,-1).value = myItemNo then if isnumeric(cell) then mytotal = myTotal + cell.Value End if end if Next -- Regards, Tom Ogilvy "Malcom" wrote in message ... Hi Tom, This will be very much usefull to me. But this may not work as required. When the code runs, active sheet may be different then this sheet(named master, where all the three columns are). Also I have to each and every rows and not every third row. This is where I could not understand the code statement . set rng = Range(Cells(1,3),Cells(rows.count,3).end(xlup)) There are other things as well which are confusing. Say, You have set a fixed range where as my data is going to be updated every 30mins with about 50-60 rows(no of rows added are not fixed). So how to update range with latest range? Can you please elaborate and explaine in more detail? Regards, Tom Ogilvy wrote in message ... set rng = Range(Cells(1,3),Cells(rows.count,3).end(xlup)) for each cell in rng if cell.offset(0,-2) < myDate and cell.Offset(0,-1).value = myItemNo then if isnumeric(cell) then mytotal = myTotal + cell.Value End if end if Next if a worksheet formula =Sumproduct(-(A1:A100<F1),-(B1:B100=G1),C1:C100) F1 contains the date G1 contains the item number -- Regards, Tom Ogilvy "Shetty" wrote in message om... Greetings, I need to write the code for the sum with criteria of two different columns. Say for example, Col A has Date, Col B has item no and col C has a value to be summed up. I have an input box for the date and item no. What I need to do is to lookup for date less the date of input, item no equall to item no of input in the same row and if both are matching then sum the value. I can do it for one criteria but not for the two criteria. Request help please. Regards, Shetty. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for SUM with duel criteria
Well,
I have tried both the VBA code and formula. But the code wont work as per the date inputed. It gived the total sum of the column B irrespective of the date. Am I missing something obvious? Regards, Tom Ogilvy wrote in message ... For both the code and the formula, yes. in the formula, if you have an odd number of conditions you need two negation operators on at least one of the conditions. =Sumproduct(-(A1:A100<F1),-(B1:B100=G1),--(C1:C100=H1),D1:D100) in the code, just connect the conditions with And. -- Regards, Tom Ogilvy "shetty" wrote in message ... Hi Tom, Its great. My problem is solved. However, just for acadamic intreste, is it possible to have more criteria then just two? Thanks and Regards, Shetty Tom Ogilvy wrote in message ... set rng = Range(Cells(1,3),Cells(rows.count,3).end(xlup)) for each cell in rng if cell.offset(0,-2) < myDate and cell.Offset(0,-1).value = myItemNo then if isnumeric(cell) then mytotal = myTotal + cell.Value End if end if Next if a worksheet formula =Sumproduct(-(A1:A100<F1),-(B1:B100=G1),C1:C100) F1 contains the date G1 contains the item number -- Regards, Tom Ogilvy "Shetty" wrote in message om... Greetings, I need to write the code for the sum with criteria of two different columns. Say for example, Col A has Date, Col B has item no and col C has a value to be summed up. I have an input box for the date and item no. What I need to do is to lookup for date less the date of input, item no equall to item no of input in the same row and if both are matching then sum the value. I can do it for one criteria but not for the two criteria. Request help please. Regards, Shetty. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for SUM with duel criteria
Hi Tom,
Your formula works great for 9 criteria sum. However, how can we use it for count instead of sum? Also what will be the criteria for blank cells and nonblank cells? Is it ="" and <"" or something else? Madiya. "Malcom" wrote in message ... Well, I have tried both the VBA code and formula. But the code wont work as per the date inputed. It gived the total sum of the column B irrespective of the date. Am I missing something obvious? Regards, Tom Ogilvy wrote in message ... For both the code and the formula, yes. in the formula, if you have an odd number of conditions you need two negation operators on at least one of the conditions. =Sumproduct(-(A1:A100<F1),-(B1:B100=G1),--(C1:C100=H1),D1:D100) in the code, just connect the conditions with And. -- Regards, Tom Ogilvy "shetty" wrote in message ... Hi Tom, Its great. My problem is solved. However, just for acadamic intreste, is it possible to have more criteria then just two? Thanks and Regards, Shetty Tom Ogilvy wrote in message ... set rng = Range(Cells(1,3),Cells(rows.count,3).end(xlup)) for each cell in rng if cell.offset(0,-2) < myDate and cell.Offset(0,-1).value = myItemNo then if isnumeric(cell) then mytotal = myTotal + cell.Value End if end if Next if a worksheet formula =Sumproduct(-(A1:A100<F1),-(B1:B100=G1),C1:C100) F1 contains the date G1 contains the item number -- Regards, Tom Ogilvy "Shetty" wrote in message om... Greetings, I need to write the code for the sum with criteria of two different columns. Say for example, Col A has Date, Col B has item no and col C has a value to be summed up. I have an input box for the date and item no. What I need to do is to lookup for date less the date of input, item no equall to item no of input in the same row and if both are matching then sum the value. I can do it for one criteria but not for the two criteria. Request help please. Regards, Shetty. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for SUM with duel criteria
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I view two sheets with duel monitors? | Excel Discussion (Misc queries) | |||
Can you open 2 windows of excel with duel screens? | Excel Discussion (Misc queries) | |||
Inputting criteria into a macro/vb code | Excel Discussion (Misc queries) | |||
Problem with criteria when using it from VBA Code | Excel Worksheet Functions | |||
Code to allow user to enter criteria for autofilter | Excel Programming |