How do I correct error using the =IF(AND(formula in excel?

Hello, can you help?

I would like to correct the following formula...This is what I have...

=IF(B1:B4,">=01/01/06"),THENCOUNT,(C1:C4,IF=A1))

This is what my worksheet shows.....

Column A Column B Column C Column D
A 12/20/05 A (Above Formula)..This
should=2
B 12/21/05 C
C 01/03/06 A
D 01/21/06 A


Can you please help me in how to write the formula correctly???

Thanks so much!

=SUMPRODUCT(--(B1:B4>=--"2006-01-01"),--(C1:C4="A"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Redwing ML" <Redwing ML@discussions.microsoft.com> wrote in message
news:3AD19B15-5921-4C7D-9921-A1595845AA3E@microsoft.com...
> Hello, can you help?
>
> I would like to correct the following formula...This is what I have...
>
> =IF(B1:B4,">=01/01/06"),THENCOUNT,(C1:C4,IF=A1))
>
> This is what my worksheet shows.....
>
> Column A Column B Column C Column D
> A 12/20/05 A (Above
Formula)..This
> should=2
> B 12/21/05 C
> C 01/03/06 A
> D 01/21/06 A
>
>
> Can you please help me in how to write the formula correctly???
>
> Thanks so much!

=SUMPRODUCT(--(B1:B4>=DATE(2006,1,1))*--(C1:C4=A1))

"Redwing ML" wrote:

> Hello, can you help?
>
> I would like to correct the following formula...This is what I have...
>
> =IF(B1:B4,">=01/01/06"),THENCOUNT,(C1:C4,IF=A1))
>
> This is what my worksheet shows.....
>
> Column A Column B Column C Column D
> A 12/20/05 A (Above Formula)..This
> should=2
> B 12/21/05 C
> C 01/03/06 A
> D 01/21/06 A
>
>
> Can you please help me in how to write the formula correctly???
>
> Thanks so much!

Are you trying to count how many values in C are = or > 01/01/2006?
If so, =COUNTIF(B1:B4,">=01/01/2006")

If you want to count with more than one criteria (say B >= a date and A = C)
=SUMPRODUCT(--(B1:B10>=DATE(2006,1,1)), --(A1:A10=C1:C10))
(I used 10 rows to test the data; I use DATE since I work with the other
date system - dd/mm/yyyy)
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Redwing ML" <Redwing ML@discussions.microsoft.com> wrote in message
news:3AD19B15-5921-4C7D-9921-A1595845AA3E@microsoft.com...
> Hello, can you help?
>
> I would like to correct the following formula...This is what I have...
>
> =IF(B1:B4,">=01/01/06"),THENCOUNT,(C1:C4,IF=A1))
>
> This is what my worksheet shows.....
>
> Column A Column B Column C Column D
> A 12/20/05 A (Above
> Formula)..This
> should=2
> B 12/21/05 C
> C 01/03/06 A
> D 01/21/06 A
>
>
> Can you please help me in how to write the formula correctly???
>
> Thanks so much!

No need for double negatives when you use * operator, only when the two
arrays are set apart by comma as in
=SUMPRODUCT(--(B1:B4>=DATE(2006,1,1)),--(C1:C4=A1))
See http://mcgimpsey.com/excel/formulae/doubleneg.html

wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
news:028BFF46-C986-40BF-A785-20794472FD0A@microsoft.com...
> =SUMPRODUCT(--(B1:B4>=DATE(2006,1,1))*--(C1:C4=A1))
>
>!

Thanks Bernard

"Bernard Liengme" wrote:

> No need for double negatives when you use * operator, only when the two
> arrays are set apart by comma as in
> =SUMPRODUCT(--(B1:B4>=DATE(2006,1,1)),--(C1:C4=A1))
> See http://mcgimpsey.com/excel/formulae/doubleneg.html
>
> wishes
>
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
> news:028BFF46-C986-40BF-A785-20794472FD0A@microsoft.com...
> > =SUMPRODUCT(--(B1:B4>=DATE(2006,1,1))*--(C1:C4=A1))
> >
> >!
>
>
>

Thank you again for the help but, it must be me? Iam still trying to get your
suggestion to work completely.

Needed=IF(B1:B4,">=01/01/06"),THENCOUNT,(C1:C4,IF=A1))

Your way =SUMPRODUCT(--(B1:B10>=DATE(2006,1,1)), --(A1:A10=C1:C10))does
count but, if i change the actual date in a cell from B to be less than the
date of 2006,1,1 the quantity does not adjust?

Can you help again??

Thanks

"Bernard Liengme" wrote:

> Are you trying to count how many values in C are = or > 01/01/2006?
> If so, =COUNTIF(B1:B4,">=01/01/2006")
>
> If you want to count with more than one criteria (say B >= a date and A = C)
> =SUMPRODUCT(--(B1:B10>=DATE(2006,1,1)), --(A1:A10=C1:C10))
> (I used 10 rows to test the data; I use DATE since I work with the other
> date system - dd/mm/yyyy)
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Redwing ML" <Redwing ML@discussions.microsoft.com> wrote in message
> news:3AD19B15-5921-4C7D-9921-A1595845AA3E@microsoft.com...
> > Hello, can you help?
> >
> > I would like to correct the following formula...This is what I have...
> >
> > =IF(B1:B4,">=01/01/06"),THENCOUNT,(C1:C4,IF=A1))
> >
> > This is what my worksheet shows.....
> >
> > Column A Column B Column C Column D
> > A 12/20/05 A (Above Formula)..should=2
> > B 12/21/05 C
> > C 01/03/06 A
> > D 01/21/06 A
> >
> >
> > Can you please help me in how to write the formula correctly???
> >
> > Thanks so much!
>
>
>