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))

 

[Bob Phillips]

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


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[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)

 

[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))

 

[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]

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