Re: Code for SUM with duel criteria

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Malcom (malcom_at_netizen.com)
Date: 04/03/04 

Date: Sun, 4 Apr 2004 00:02:59 +0530

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 <twogilvy@msn.com> wrote in message
news:106tonmb7dinq8d@news.supernews.com...
> 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" <shetty@gawab.com> wrote in message
> news:3a5ebb1a.0404030751.59c481a4@posting.google.com...
> > 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.
>
>

Relevant Pages

  • Re: Code for SUM with duel criteria
    ... "shetty" wrote in message ... > However, just for acadamic intreste, is it possible to have more criteria ... > Thanks and Regards, ... > Tom Ogilvy wrote in message ...
    (microsoft.public.excel.programming)
  • Re: How do I conditionally build worksheet from existing worksheet?
    ... Use a custom filter on the quantity column and set up your criteria. ... Regards, ... Tom Ogilvy ... > I want to build an inventory worksheet that contains only the items whose ...
    (microsoft.public.excel.misc)
  • Re: Code for SUM with duel criteria
    ... Your formula works great for 9 criteria sum. ... Also what will be the criteria for blank cells and nonblank cells? ... >> Regards, ...
    (microsoft.public.excel.programming)
  • Re: formula gone array
    ... assumed it worked as written, but gave a count instead of a sum. ... > *** John ... >>Tom Ogilvy ... >>> to meet the criteria in order for the correct numbers to ...
    (microsoft.public.excel.programming)
  • Re: how have status bar "Sum=xxxxxx" use commas=>"Sum=xxx,xxx"?
    ... format the source cells that you are highlighting and the sum will reflect ... Regards, ... Tom Ogilvy ...
    (microsoft.public.excel.programming)