Problem with COUNTIF

  • Thread starter Thread starter Iris
  • Start date Start date
I

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?
 
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)
 
I am using
=COUNTIF(Enquiries!D: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
 
Matt,

Try this

=SUMPRODUCT((TEXT(D1:D100,"mmmm")="January")*(NOT(ISBLANK(D1:D100))))

or this

=SUMPRODUCT((MONTH(D1:D100)=1)*(NOT(ISBLANK(D1:D100))))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
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(D1:D1000)=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,

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

--
Regards
Roger Govier
Roger Govier said:
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(D1:D1000)=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
Click to expand...
 
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
 
Hi
just use the following:
=SUMPRODUCT((MONTH(D1:D100)=1)*(NOT(ISBLANK(D1:D100)))*(G1:G1000))
or
=SUMPRODUCT((MONTH(D1:D100)=1)*(NOT(ISBLANK(D1:D100))),G1:G1000)

If G is the column to sum
Frank
 
I'm new to this forum and already learning heaps. This one refers to
problem I was having but I'm not sure how to use it where differen
years apply. I need to be able to differentiate between a January 0
date and a January 04 date. Will SUMPRODUCT do this? Can anybody help?
 
Back
Top