Frequency and match formula

[cstickman]

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))

 

[Drrellik]

Excel 2012
	N	O
2	5
3	6
4	7
5	8
6	9
7	10
8	11
9	12
10	13
11		9
Sheet1

Cell Formulas
Range	Formula
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]

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]

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]

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:

=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))