VB multiple criteria countif change event

[ram]

I was looking for help on the following:

Cell A1 is a sum of range b1:b10.
When the value in cell a1 changes i would like the following to happen in
cell C1

give me A count from a range where the date = 1/1/2006 and total >0

Date Total
1/1/2006 60
2/1/2006 30
1/1/2006 0

in this example C1 would be 60

Thanks for nay help

[Tom Ogilvy]

=sumproduct(--(B1:B10=DateValue("1/1/2006")),--(C1:C10>0),C1:C10)

if values in C1:C10 will always be positive or zero

=Sumif(B1:B10,"1/1/2006",C1:C10)

--
Regards,
Tom Ogilvy


"ram" wrote:

> I was looking for help on the following:
>
> Cell A1 is a sum of range b1:b10.
> When the value in cell a1 changes i would like the following to happen in
> cell C1
>
> give me A count from a range where the date = 1/1/2006 and total >0
>
> Date Total
> 1/1/2006 60
> 2/1/2006 30
> 1/1/2006 0
>
> in this example C1 would be 60
>
> Thanks for nay help
>
>
>
>

[ram]

HI Tom,

Thanks for the response, however i made an error in my post.
in the example the result for c1 should be 1

Thanks for any help



"Tom Ogilvy" wrote:

> =sumproduct(--(B1:B10=DateValue("1/1/2006")),--(C1:C10>0),C1:C10)
>
> if values in C1:C10 will always be positive or zero
>
> =Sumif(B1:B10,"1/1/2006",C1:C10)
>
> --
> Regards,
> Tom Ogilvy
>
>
> "ram" wrote:
>
> > I was looking for help on the following:
> >
> > Cell A1 is a sum of range b1:b10.
> > When the value in cell a1 changes i would like the following to happen in
> > cell C1
> >
> > give me A count from a range where the date = 1/1/2006 and total >0
> >
> > Date Total
> > 1/1/2006 60
> > 2/1/2006 30
> > 1/1/2006 0
> >
> > in this example C1 would be 60
> >
> > Thanks for nay help
> >
> >
> >
> >

[Tom Ogilvy]

=sumproduct(--(B1:B10=DateValue("1/1/2006")),--(C1:C10>0))

--
Regards,
Tom Ogilvy



"ram" wrote:

> HI Tom,
>
> Thanks for the response, however i made an error in my post.
> in the example the result for c1 should be 1
>
> Thanks for any help
>
>
>
> "Tom Ogilvy" wrote:
>
> > =sumproduct(--(B1:B10=DateValue("1/1/2006")),--(C1:C10>0),C1:C10)
> >
> > if values in C1:C10 will always be positive or zero
> >
> > =Sumif(B1:B10,"1/1/2006",C1:C10)
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "ram" wrote:
> >
> > > I was looking for help on the following:
> > >
> > > Cell A1 is a sum of range b1:b10.
> > > When the value in cell a1 changes i would like the following to happen in
> > > cell C1
> > >
> > > give me A count from a range where the date = 1/1/2006 and total >0
> > >
> > > Date Total
> > > 1/1/2006 60
> > > 2/1/2006 30
> > > 1/1/2006 0
> > >
> > > in this example C1 would be 60
> > >
> > > Thanks for nay help
> > >
> > >
> > >
> > >

[ram]

I have the formula working correctly, however, when I run the macros they are
moving very slow.

Any suggestion on how i can correct this problem?


Thanks



"Tom Ogilvy" wrote:

> =sumproduct(--(B1:B10=DateValue("1/1/2006")),--(C1:C10>0),C1:C10)
>
> if values in C1:C10 will always be positive or zero
>
> =Sumif(B1:B10,"1/1/2006",C1:C10)
>
> --
> Regards,
> Tom Ogilvy
>
>
> "ram" wrote:
>
> > I was looking for help on the following:
> >
> > Cell A1 is a sum of range b1:b10.
> > When the value in cell a1 changes i would like the following to happen in
> > cell C1
> >
> > give me A count from a range where the date = 1/1/2006 and total >0
> >
> > Date Total
> > 1/1/2006 60
> > 2/1/2006 30
> > 1/1/2006 0
> >
> > in this example C1 would be 60
> >
> > Thanks for nay help
> >
> >
> >
> >

[Tom Ogilvy]

If you think it is calculation slowing you down, try turning it off when you
run you macro. At the top

Application.Calculation = xlManual
Application.ScreenUpdating = False
Activesheet.DisplayPageBreaks = False
' your code


' don't turn the pagebreaks back on.
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

--
Regards,
Tom Ogilvy

"ram" <[email protected]> wrote in message
news:[email protected]...
> I have the formula working correctly, however, when I run the macros they
are
> moving very slow.
>
> Any suggestion on how i can correct this problem?
>
>
> Thanks
>
>
>
> "Tom Ogilvy" wrote:
>
> > =sumproduct(--(B1:B10=DateValue("1/1/2006")),--(C1:C10>0),C1:C10)
> >
> > if values in C1:C10 will always be positive or zero
> >
> > =Sumif(B1:B10,"1/1/2006",C1:C10)
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "ram" wrote:
> >
> > > I was looking for help on the following:
> > >
> > > Cell A1 is a sum of range b1:b10.
> > > When the value in cell a1 changes i would like the following to happen
in
> > > cell C1
> > >
> > > give me A count from a range where the date = 1/1/2006 and total >0
> > >
> > > Date Total
> > > 1/1/2006 60
> > > 2/1/2006 30
> > > 1/1/2006 0
> > >
> > > in this example C1 would be 60
> > >
> > > Thanks for nay help
> > >
> > >
> > >
> > >