Counting value ranges

Hi,

I have two columns (categories) in Excel that have numerical values such as this:

Employee | Revenue / Expenses | Sales / Expenses
John 2.5 1.4

I want to create a formula that lets me count # of occurrences for a specific range for both categories *across the entire data set* i.e. looking down both columns, for example

Revenue / Expenses of range of 1-2
Sales / Expenses range of 2-3

and so forth

How can I do something like this?

Couldn't you add two COUNTIF functions? One for Revenue and one for Expenses.

COUNTIFS with two criteria statements only works when the range is for example greater than or less than a certain number. It doesn't work when I need a range between TWO numbers. Here's a subset of how my data looks like:

Rev/Exp Sales/Exp
6.1 0.5
4.4 0.5
2.7 0.5
1.9 0.8
2.9 0.8
2.8 0.9
2.3 0.4
0.6 0.7
4.5 0.5
2.3 0.5
3.2 0.6
3.5 0.5
2.0 0.9
2.7 0.7
2.7 0.5
3.2 0.6
5.0 0.6
2.9 0.6
0.3 0.7
2.1 1.1
2.7 0.6
3.8 0.9
3.8 0.9
3.5 0.6
3.6 0.7
1.7 0.6
4.0 0.6

Basically from the above data set I'm trying to see how many occurrences there are when revenue/exp is between 1 and 2 AND sales/exp is between 1 and 2

Sorry, I didn't realize you wanted a count when the criteria was true for each row. I would use the SUMPRODUCT function. I am going to use Re to represent the revenue/exp range and Se to represent the sales/exp range in the following formula and the criteria from your last post.

SUMPRODUCT((Re>1)*(Re<2),(Se>1)*(Se<2))

I think this formula will accomplish what you want.

Originally Posted by matt.nicolai

Sorry, I didn't realize you wanted a count when the criteria was true for each row. I would use the SUMPRODUCT function. I am going to use Re to represent the revenue/exp range and Se to represent the sales/exp range in the following formula and the criteria from your last post.

SUMPRODUCT((Re>1)*(Re<2),(Se>1)*(Se<2))

I think this formula will accomplish what you want.

Thanks, that works when both of the numbers need to be in a range.

What if I have an example where it's:

Rev/Exp < 1
NPV/Exp 1-2