View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
Cortez Cortez is offline
external usenet poster
 
Posts: 38
Default Multi-demensional frequency distribution

I'm looking for a formula that can display the number of people whose account activity matches two sets of criteria.

Countifs() and Frequency() come close, but can't quite do what I need. (Or, I can't figure out how to use them effectively.)

I have two data columns, 5 rows each, each row representing a unique person:
A1:A5 - closing bank account balances
B1:B5 - total account deposits

A
2000.00
2500.00
1500.00
500.00
3000.00

B
100.00
200.00
0.00
100.00
400.00

I want to be able to count the number of people who satisfy the following criteria:
C1: Account Balance < 2500 and Deposits < 250
C2: Account Balance = 2500 and Deposits < 250
D1: Account Balance < 2500 and Deposits = 250
D2: Account Balance = 2500 and Deposits = 250

If I use the FREQUENCY array function I can validate one criteria only. I can either determine the frequency of Account balance meeting the criteria OR Deposits, but not both.

I also tried using COUNTIFS like:

C1:=countifs(A1:A5,"=0",A1:A5,"<2500",B1:B5,"=0" ,B1:B5,"<250")

However, this doesn't give an accurate result for between 0 to 250 AND between 0 to 2500. I understand why this doesn't work, but I wanted to give an idea of what I am trying to accomplish.

If anyone understands what I am trying to do, your help would be greatly appreciated.

Thanks,
TK