Problem with COUNTIF

[Iris]

here goes:

A1:A8 contains numbers. A9 Contains the average of these
numbers.

I am trying to count the number of times that any data in
A1:A8 was anumber larger then the average.

Using COUTIF(A1:A8, ">A9") returns a 0

Any ideas anyone?

 

[Biff]

Hi Iris,

Try this:

=COUNTIF(A1:A8,">"&A9)

Biff

 

[Frank Kabel]

Hi Iris
try
=COUNTIF(A1:A8,">" & A9)

HTH
Frank

 

[Bob Phillips]

Iris,

Here is a slight alternative whereby you don't need the average in A9

=COUNTIF(A1:A8,">"&AVERAGE(A1:A8))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[matt_the_brum]

I am using
=COUNTIF(Enquiries!D,"Jan-04")
to try and count hoe many cells from another worksheet include
January date. It is only looking for exact matches to "Jan-04" wher
as I want it to pick up all Jan dates eg 02-Jan-04, 15-Jan-04 etc.
Any suggestions

 

[Bob Phillips]

Matt,

Try this

=SUMPRODUCT((TEXT(D1100,"mmmm")="January")*(NOT(ISBLANK(D1100))))

or this

=SUMPRODUCT((MONTH(D1100)=1)*(NOT(ISBLANK(D1100))))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Roger Govier]

Hi Matt
Whilst you see the dates as 02-Jan-04 they are not helf as text, but as a
serial number.
Countif is looking ofr the text enclosed within your quotes hence not
finding any matches

Instead you could try
=SUMPRODUCT(--(MONTH(D11000)=1))

Sumproduct will not take a complete column as its range, so change the range
to include the maximum number of cells likely to contain your data

 

[Roger Govier]

Matt,

Bob is far more awake than I am this morning.
His formula =SUMPRODUCT((MONTH(D1100)=1)*(NOT(ISBLANK(D1100))))
is correct as the blank cells would evaluate to a Month of 1 and would mess
up your answer.

--
Regards
Roger Govier

Hi Matt
Whilst you see the dates as 02-Jan-04 they are not helf as text, but as a
serial number.
Countif is looking ofr the text enclosed within your quotes hence not
finding any matches

Instead you could try
=SUMPRODUCT(--(MONTH(D11000)=1))

Sumproduct will not take a complete column as its range, so change the range
to include the maximum number of cells likely to contain your data

 

[matt_the_brum]

Thanks you lot, now seems to be working. Next step is to sum all th
cells 3 colums to the right of the ones that have just been counted a
January. This might get a bit complicated so will probably just resor
to manual selection of cells

 

[Frank Kabel]

Hi
just use the following:
=SUMPRODUCT((MONTH(D1100)=1)*(NOT(ISBLANK(D1100)))*(G1:G1000))
or
=SUMPRODUCT((MONTH(D1100)=1)*(NOT(ISBLANK(D1100))),G1:G1000)

If G is the column to sum
Frank