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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Code for SUM with duel criteria
 

Please ! ! !
Can somebody help me?
How can we use Tom's formula for count instead of sum?
Please!!!!
Regards,
Madiya

(Madiya) wrote in message . com...
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.