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

 

[craig80]

But sumifs does work?

Just not count. That's strange.... Oh well, I need to brush up on series then.

Thanks.

C

 

[craig80]

Had to split up the range... and settle with this.

Thanks for everyones input.
'=SUM(COUNTIF(QA,">10"),COUNTIF(CA,"<10"),COUNTIF(SA, "<10"),COUNTIF(EA, "<10"),COUNTIF(ENGA,"<10"), COUNTIF(PLA, "<10"))

Craig

 

[Aladin Akyurek]

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

Try...

=INDEX(FREQUENCY(Actual,10),2)+1

 

[craig80]

Works a treat aladin.

Any thoughts on if i needed to do a SUMIF? where would it sit?

I have had to build...break the range down to this...messy

=SUM(COUNTIFS(QP,">10",DIST, $A$1),COUNTIFS(CP,">10",DIST, $A$1),COUNTIFS(SP,">10",DIST, $A$1),COUNTIFS(EP,">10",DIST, $A$1),COUNTIFS(ENGP,">10",DIST, $A$1),COUNTIFS(PLP,">10",DIST, $A$1))

 

[Aladin Akyurek]

Works a treat aladin.

Any thoughts on if i needed to do a SUMIF? where would it sit?

I have had to build...break the range down to this...messy

=SUM(COUNTIFS(QP,">10",DIST, $A$1),COUNTIFS(CP,">10",DIST, $A$1),COUNTIFS(SP,">10",DIST, $A$1),COUNTIFS(EP,">10",DIST, $A$1),COUNTIFS(ENGP,">10",DIST, $A$1),COUNTIFS(PLP,">10",DIST, $A$1))

This thread left me with an uneasy feeling...

What is the range QP stands for or the cells which make up QP? Same question reagrding DIST. And what is in $A$1?

 

[craig80]

QP refers to a range of cash values, (calculated by VB) and dist represents another range, "1" or "3" (east or west from the original data set) $A$1 refers to which i would like to seach for? 1 or 3? EAST OR WEST?

I hope that all makes sense.
C

 

[Aladin Akyurek]

QP refers to a range of cash values, (calculated by VB) and dist represents another range, "1" or "3" (east or west from the original data set) $A$1 refers to which i would like to seach for? 1 or 3? EAST OR WEST?

I hope that all makes sense.
C

Craig: Alas, you are not answering my questions. QP is a named range, right? So, that name must stand for a range or for a set of disjoint (non-neighboring) cells. So, I just wanted to know what QP refers to. The same question hold for DIST, and also I wanted the value A1 houses.

 

[craig80]

Aladin:

QP refers to one column of cash values

ACTUAL is a range that refers to a set of disjointed columns of cash values.

I am trying to count cells in ACTUAL, where there is a cash value greater than 10. I need to argue it against some Single values, i.e. which side of the country this is and the range DIST identifys this, and $A$1 houses this.

To add to the drama, I also need to refer actual with a date range...

=SUMIFS(Actual,">="&C2,Actual,"<="&C2+6,DIST, $A$1)

If there is any way to include your Index(FREQUENCY( function, it would be great. I tried =SUMIFS(INDEX(FREQUENCY(Actual,10),2),Actual,">="&C2,Actual,"<="&C2+6)

But it returns a #value error.

Hope that all makes sense.

Thanks in advance, craig