counting the number of entries with conditions

[AHALEY]

I have a group of data, and I am trying to provide a simple count of the number of entries that are equal to or less than 100, less than 200 but greater than 100, etc. I have brain lock around COUNT() COUNTIF() and IF statements and am having issues working out how to get these to do what I am needing.

In addition, I am trying to average each row of data, and providing a running average for the entire series of data, but need to exclude in the average calculation any entries that are either 0 or #N/A.

I'm sure these are fairly simple problems, but I am stumped....

Any help would be appreciated

 

[Legacy 185660]

These are all CSE formulas, meaning after you type them in you have to press Ctrl, Shift, and Enter

Less then 100
=COUNT(IF(A2:A5<=100,A2:A5))

Between 100-200
=COUNT(IF((A2:A5<=200)*(A2:A5>=100),A2:A5))

 

[AHALEY]

Thanks. Worked beautifully.

 

[Legacy 185660]

No problem! I've just started to learn how to use CSE formulas

 

[T. Valko]

I have a group of data, and I am trying to provide a simple count of the number of entries that are equal to or less than 100, less than 200 but greater than 100, etc. I have brain lock around COUNT() COUNTIF() and IF statements and am having issues working out how to get these to do what I am needing.

In addition, I am trying to average each row of data, and providing a running average for the entire series of data, but need to exclude in the average calculation any entries that are either 0 or #N/A.

I'm sure these are fairly simple problems, but I am stumped....

Any help would be appreciated

Try these...

Count numbers <=100...

=COUNTIF(A2:A100,"<=100")

Count numbers >100 and <=200...

=COUNTIF(A2:A100,">100")-COUNTIF(A2:A100,">200")

Average numbers >0 and ignore any errors...

Array entered**:

=AVERAGE(IF(ISNUMBER(A1:A100),IF(A1:A100>0,A1:A100)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.