MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by dave moss on August 30, 2001 7:20 AM

I'm trying to put in a string that will count the number of entries between 2 numbers in a column in Excel, so if the numbers in the column are 1 to 20 I want to know how many are greater than 5 but less than 15.

I've tried all the variations on Countif I can think of but am not having much joy.

Any suggestions gratefully received.


Posted by Aladin Akyurek on August 30, 2001 7:31 AM

A multiconditional requires a different approach:


=SUMPRODUCT((A1:A100 > 5)*(A1:A100 < 15))


Posted by Waynef on August 30, 2001 7:37 AM

Hi dave,

I have this from the help wizard in MS office...

Summarize values that meet conditions by using the Conditional Sum Wizard
If you want to summarize values in a list based on specific conditions, you can use the Conditional Sum Wizard. For example, if your list contains sales amounts for different salespeople, the Conditional Sum Wizard can help you create a formula that calculates the total sales amount for one salesperson.

The Conditional Sum Wizard is an add-in program. If the Conditional Sum command is not on the Wizard submenu on the Tools menu, you need to install and load the add-in program. For more information about add-in programs and how to install and load them, click .

Click a cell in the list.

On the Tools menu, point to Wizard, and then click Conditional Sum.

Follow the instructions in the wizard.

The formulas created by the Conditional Sum Wizard are array formulas. After you edit these formulas, you must press CTRL+SHIFT+ENTER to lock in the formulas.

Learn about how conditional formulas work.

Posted by dave moss on August 31, 2001 3:01 AM

Thanks Aladin,

That works a treat