MrExcel.com - Your One Stop for Excel Tips & Solutions

Count of specified range of numbers

Posted by Diederick on July 13, 2001 5:20 AM

All,

Can anybody help me with the following, because I cannot find a simple solution:

I have a column with numbers. I want to count how many times a number between 5 and 10 exists in the column.

This formula works:
COUNTIF(F1:F200;<5) to count all the number smaller than 5. But how to do this when I want to count the values >5 and <10?

Thanks alot in advance.

Diederick

Posted by Aladin Akyurek on July 13, 2001 6:17 AM

Given that the range F1:F200 is not too big, use the following array formula:

=SUM((F1:F200>=5)*(F1:F200<=10))

You need to hit CONTROL+SHIFT+ENTER at the same time (not just ENTER) to enter an array formula.


Aladin

Posted by Daniel on July 13, 2001 8:05 AM

Array formula

I had a simular problem and also found help in an array formular. But can anybody explain me what an array formula is? What is the difference to a normal formular? (Unfortunately I cannot find any help in the excel-helping-files; I only now the term 'array' in the context of programming languages, but here it is supposed to have another meaning I think)
TNX in advance

Posted by Aladin Akyurek on July 13, 2001 9:00 AM

Re: Array formula

Daniel,

You can find an explanation of how array formulas work in a thread that occurred at this board in Archive2. Search/find via my name would take some time but it would help. If that fails or the explanation is too sketchy, I'll make another attempt to explain.

Aladin

Posted by Diederick on July 16, 2001 6:08 AM

Re: Array formula

Aladin,

Thanks very much for your advice. I haven't seen the join using SUM and the * before. Do you know where I can find any information on this?

Regards,

Diederick

Posted by Diederick on July 16, 2001 6:08 AM

Re: Array formula

Aladin,

Thanks very much for your advice. I haven't seen the join using SUM and the * before. Do you know where I can find any information on this?

Regards,

Diederick

Posted by Diederick on July 16, 2001 6:27 AM

Re: Array formula

Aladin,

You can forget my message which I posted recently. I understand now what you did with the formula. Great thinking. I will use this in the future!

Diederick Aladin, Thanks very much for your advice. I haven't seen the join using SUM and the * before. Do you know where I can find any information on this? Regards,