On Thursday, January 24, 2013 2:29:18 PM UTC-6, Cortez wrote:
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

I've resolved my issue. Thanks for your assistance.