help with countif formula

[Duncan]

Hi guys,

im trying to calculate amount of entries by area and within calender
year, ive tried two differant formulas already but i cant get it to
check the area AND the start date,

below are the two formulas that are not giving me the right result,
hoping someone can help!

=COUNTIF(data!E2:E65536,"East")+AND(COUNTIF(data!E2:E65536,">01/01/2006"))

or

=COUNTIF(data!E2:E65536,AND("east",">38717"))

(38717 is the number for 01/01/2006)

[Duncan]

ive also just tried this and it doesnt work either

=SUMPRODUCT(--(data!E2:E65536=(TEXT("east","east"))),--(data!E2:E65536=">38717"))

[Arvi Laanemets]

Hi

What about
=SUMPRODUCT(--(data!E2:E65536="east"),--(data!E2:E65536 >38717))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )


"Duncan" <[email protected]> wrote in message
news:[email protected]...
> ive also just tried this and it doesnt work either
>
> =SUMPRODUCT(--(data!E2:E65536=(TEXT("east","east"))),--(data!E2:E65536=">38717"))
>

[Bob Phillips]

=SUMPRODUCT(--(data!E2:E65536="east"),--(data!E2:E65536=--"2006-01-01"))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Arvi Laanemets" <[email protected]> wrote in message
news:%[email protected]...
> Hi
>
> What about
> =SUMPRODUCT(--(data!E2:E65536="east"),--(data!E2:E65536 >38717))
>
>
> --
> Arvi Laanemets
> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>
>
> "Duncan" <[email protected]> wrote in message
> news:[email protected]...
> > ive also just tried this and it doesnt work either
> >
> >
=SUMPRODUCT(--(data!E2:E65536=(TEXT("east","east"))),--(data!E2:E65536=">387
17"))
> >
>
>

[Bob Phillips]

Just realised that they are the same column so you must want an OR condition

=SUMPRODUCT((data!E2:E65="east")+(((data!E2:E65="east"))*(data!E2:E65>=--"20
06-01-01")))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> =SUMPRODUCT(--(data!E2:E65536="east"),--(data!E2:E65536=--"2006-01-01"))
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Arvi Laanemets" <[email protected]> wrote in message
> news:%[email protected]...
> > Hi
> >
> > What about
> > =SUMPRODUCT(--(data!E2:E65536="east"),--(data!E2:E65536 >38717))
> >
> >
> > --
> > Arvi Laanemets
> > ( My real mail address: arvi.laanemets<at>tarkon.ee )
> >
> >
> > "Duncan" <[email protected]> wrote in message
> > news:[email protected]...
> > > ive also just tried this and it doesnt work either
> > >
> > >
>
=SUMPRODUCT(--(data!E2:E65536=(TEXT("east","east"))),--(data!E2:E65536=">387
> 17"))
> > >
> >
> >
>
>

[Duncan]

nope sorry, I put the original formulas in wrong, it is looking to a
differant cell for the start date.. sorry

=SUMPRODUCT(--(data!E2:E65536="east"),--(data!i2:i65536 >38717))

this one worked for me.. can it be changed so instead of >38717 it does
=year(Start Date) = 2006 ?

[Arvi Laanemets]

Hi


=SUMPRODUCT(--(data!E2:E65536="east"),--(YEAR(data!i2:i65536 )>2006))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"Duncan" <[email protected]> wrote in message
news:[email protected]...
> nope sorry, I put the original formulas in wrong, it is looking to a
> differant cell for the start date.. sorry
>
> =SUMPRODUCT(--(data!E2:E65536="east"),--(data!i2:i65536 >38717))
>
> this one worked for me.. can it be changed so instead of >38717 it does
> =year(Start Date) = 2006 ?

[Duncan]

Brilliant, Thank you Arvi!

[Bob Phillips]

Shame, I liked my solution <vbg>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Duncan" <[email protected]> wrote in message
news:[email protected]...
> nope sorry, I put the original formulas in wrong, it is looking to a
> differant cell for the start date.. sorry
>
> =SUMPRODUCT(--(data!E2:E65536="east"),--(data!i2:i65536 >38717))
>
> this one worked for me.. can it be changed so instead of >38717 it does
> =year(Start Date) = 2006 ?
>

[Duncan]

Sorry Bob, Many thanks for your help also!

I feel really bad now.... its only because I was reading Arvi's post as
I wrote my reply..

Sorry again........your solution did work though!!

("humble smile")

Duncan

[Bob Phillips]

it's all right, I was just feeling pleased with myself for noticing that you
used the same column and so it needed an OR condition, and for catching that
east passed the greater than date test. My humility has been restored <vbg>

Bob

"Duncan" <[email protected]> wrote in message
news:[email protected]...
> Sorry Bob, Many thanks for your help also!
>
> I feel really bad now.... its only because I was reading Arvi's post as
> I wrote my reply..
>
> Sorry again........your solution did work though!!
>
> ("humble smile")
>
> Duncan
>