# Frequency and match formula

#### cstickman

##### Board Regular
Hello, I am using the below formula to take mulitple addresses and count them as one. The formula works great, but I need to add one more criteria to it. The report I am pulling within my company is data for all locations. So I need to be able to break it apart by account number. So I need to add to the IF statement account number first and then run the formula. Any help would be greatly appreciated. Thanks

=SUM(IF(FREQUENCY(MATCH(N2:N10,N2:N10,0),MATCH(N2:N10,N2:N10,0))>0,1))

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Drrellik

##### Well-known Member

Excel 2012
NO
25
36
47
58
69
710
811
912
1013
119
Sheet1
Cell Formulas
RangeFormula
O11=SUM(IF(FREQUENCY(MATCH(N2:N10,N2:N10,0),MATCH(N2:N10,N2:N10,0))>0,1))
this is just a repost of your data and your formula, can you explain what is it you want a little better and where the account number is in order to help you better.

#### Aladin Akyurek

##### MrExcel MVP
Hello, I am using the below formula to take mulitple addresses and count them as one. The formula works great, but I need to add one more criteria to it. The report I am pulling within my company is data for all locations. So I need to be able to break it apart by account number. So I need to add to the IF statement account number first and then run the formula. Any help would be greatly appreciated. Thanks

=SUM(IF(FREQUENCY(MATCH(N2:N10,N2:N10,0),MATCH(N2:N10,N2:N10,0))>0,1))

What is the range for account numbers?

#### cstickman

##### Board Regular
Thanks for responding - I will try and answer both questions with this reply. The range will be different every report so it would be nice just to do the whole column M.

What I am trying to do is we have 6 markets all over the US. They all do work for the same customer. The report I can pull to get information has all the accounts on one big excel workbook. So I would like to count how many stops per route. We have many customers who place five orders and they all deliver on the same day. So I would only want to count that as one stop. The customers name is in Column N and the account number is in M.

Thanks for responding!!

#### Aladin Akyurek

##### MrExcel MVP
Thanks for responding - I will try and answer both questions with this reply. The range will be different every report so it would be nice just to do the whole column M.

What I am trying to do is we have 6 markets all over the US. They all do work for the same customer. The report I can pull to get information has all the accounts on one big excel workbook. So I would like to count how many stops per route. We have many customers who place five orders and they all deliver on the same day. So I would only want to count that as one stop. The customers name is in Column N and the account number is in M.

Thanks for responding!!

I'm not sure whether all that information is relevant. Your original post implies to count the number of different customers associated with a account number. If so:

X2 houses an account number of interest.

Y2, control+shift+enter, not just enter:
Rich (BB code):
``````=SUM(IF(FREQUENCY(\$N\$2:\$N\$10<>"",IF(\$M\$2:\$M\$10=\$X2,
MATCH("~"&\$N\$2:\$N\$10,\$N\$2:\$N\$10&"",0))),
ROW(\$N\$2:\$N\$10)-ROW(\$N\$2)+1),1))
``````

Replies
2
Views
158
Replies
1
Views
123
Replies
2
Views
175
Replies
10
Views
147
Replies
3
Views
375

Threads
1,195,953
Messages
6,012,515
Members
441,703
Latest member
clivelincoln

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back