CountIF problems and range

[craig80]

Hi Folks,

I have a range that I would like to use to COUNT all the fields I have a value greater than 10.

I konw I can amend my VB to suit, but its proving a difficulty. Not least because i am not doing the VB element.

I have a data set that is returning values that are either

1. Greater than 0
or
2. Zero

NB: range is defined as "Actual" and cells are not classed as "Blank"

=COUNTIF(Actual, ">0")

I have tried all different combinations of syntax. But to no avail. If anyone could help, I would be very greatful. Thanks

Craig

 

[Scott Huish]

That syntax is correct, but you might want to change it to be ">10" as that's what you say you want to count.

 

[craig80]

Hotpepper,

I have changed it as you suggested. But i am still getting an error.

 

[Scott Huish]

That image is not going to show, it's a file on your M:\ drive

Excel Workbook
	A	B	C
1	15		3
2	23
3	12
4	1
5	4
6	3
7	2
8	0
9	9
Sheet1

 

[craig80]

humm.... it seems pasting in a pic is more trouble. ha.

Hotpepper - this is the formula that I have been using...

=COUNTIF(Actual,">10")

I am still getting an error #value.

I have checked the range, it has valid data in it. Also, I have other sumifs using ranges on the same spreadsheet?

C

 

[Scott Huish]

I'm sorry I can not duplicate your problem.

 

[JoeMo]

What is the error you get? It might be that your named range (Actual) is corrupted.

 

[craig80]

The range is counting and summing not problem, its when i put an IF in there.. this is what I am entering, and getting.

Input:
=COUNTIF(Actual,">10")

Output:
#value.

Thanks

Craig

 

[craig80]

I have been doing some investigating. It seems to be when use a range that has columns that are not side by side?

It might also help if i detail the range?

='DR5 Data'!$K:$K,'DR5 Data'!$N:$N,'DR5 Data'!$Q:$Q,'DR5 Data'!$T:$T,'+'DR5 Data'!$1:$1048576DR5 Data'!$W:$W,'DR5 Data'!$Z:$Z

These ranges work when i am using SUMIFS and Regular Count.

eg.
=COUNT(No_Stds_Count,"<>")
=COUNTIFS(Bus_Area, "<>", DIST, A1)

But i am having problems when doing:

=COUNTIF(Actual,">10")
=COUNTIFS(No_Stds_Count, "<>", DIST, A1)

Any help would be fantastic.

Thanks
Craig
Regular

 

[sulakvea]

you get #value! when you use non-adjacent ranges as "Actual". not just non-adjacent, but something that can not be represented with a single range expression. like, if Actual = A1:A10 it would work. if Actual = A1:B10, it would work. but if Actual = A1:A10 & B1:B5, then the formula wont work.