Hi, In the below, can you help me to put the formulas: 1. Count the number of to

Question

Hi,

In the below, can you help me to put the formulas:

1. Count the number of towns (not to count repeated towns)

2. Under JC ACH column, count no of towns where the figures are mentioned (not to count repeated towns)

Regards!!!!!

JC - 1 JC - 1 JC - 2 JC - 2 JC - 3 JC - 3 CUSTOMER NAME TOWN TGT ACH TGT ACH TGT ACH SAI RAM ENTERPRISES AHMEDABAD 0.03 -   0.04 -   0.05 -   M M AGENCIES AHMEDABAD 0.59 -   0.82 0.44 0.97 1.11 SHREEJI ENTERPRISE AHMEDABAD 0.46 -   0.64 0.47 0.76 0.57 MODI & SHAH AGENCY AHMEDABAD 2.01 1.98 2.01 1.60 2.16 3.41 SAMBHAV ENTERPRISES AMALSAD 0.11 0.14 0.15 0.14 0.17 0.12 VISHWA SALES AMRELI 0.03 -   0.04 0.09 0.05 -   JIVAN JYOT AGENCY ANAND 0.16 0.05 0.22 -   0.26 0.56 JINDUTT AGENCIES ANJAR 0.01 -   0.01 0.16 0.01 -   LABHAN NOVELTY BARDOLI 0.05 -   0.06 0.16 0.07 0.04 MAHAVIR AGENCY BARODA 0.90 0.49 0.90 1.63 0.96 2.16 VISHWA ENTERPRISE BHARUCH 0.05 0.13 0.06 -   0.07 0.08 SHREE BHATIYANI NOVELTY-BHARUCH BHARUCH 0.11 -   0.15 0.15 0.18 0.05 MANOJ TRADERS - BHAVNAGAR BHAVNAGAR 0.51 -   0.70 -   0.83 -   CHANDNI BENGALS-BODELI BODELI 0.02 -   0.03 -   0.04 -   BHAGYALAXMI BANGLES BORSAD 0.02 -   0.03 -   0.04 -   SOHAM AGENCY CHIKHLI 0.07 0.09 0.09 0.30 0.11 0.15 HARSH AGENCIES DAHOD 0.07 -   0.09 -   0.11 0.34 PRATEEGNA SALES GODHRA 0.03 -   0.04 -   0.05 0.15 DIXITA SALES AGENCIES GONDAL 0.01 -   0.01 -   0.02 -   RIDHI SIDDHI SALES JAMNAGAR 0.05 -   0.06 -   0.08 -   SHRUDDHA MARKETING JAMNAGAR 0.16 -   0.22 -   0.26 0.10 AVDHOOT TRADERS JOTANA 0.06 -   0.08 -   0.10 -   PAWAN CUTLARY STORE JUNAGADH 0.23 -   0.23 0.50 0.25 0.29 SHREE DEVKRUPA ENTEPRIRSES-KESHOD Keshod 0.06 -   0.06 -   0.07 -   MEGHA TRADERS MORBI 0.02 -   0.03 -   0.03 -   APSARA TRADING CO MORBI 0.02 -   0.03 -   0.03 -   BHARATM BANGLES NANDIAD 0.24 -   0.34 0.13 0.40 0.25 R.S.AGENCIES NAVSARI 0.26 0.30 0.36 0.18 0.43 0.36 JASH ENTERPRISE PALANPUR 0.02 -   0.02 -   0.03 -   A ONE CUTLARY STORES PORBANDER 0.20 -   0.27 0.28 0.32 -   OM ENTERPRISE RAJKOT 0.28 0.25 0.28 0.61 0.30 0.77 NAVKAR NOVELTY RAJPIPLA 0.02 0.13 0.02 0.08 0.03 0.07 SAI AGENCIES SURAT -   -   -   -   -   -   SHREE ENTERPRISE SURAT 0.06 0.22 0.09 -   0.11 0.28 SAINATH ENTERPRISES SURAT 1.26 0.90 1.26 0.85 1.35 3.48 NAVKAR BANGLES SURENDRANAGAR 0.07 -   0.10 -   0.12 -   BHARAT SALES-UNA UNA 0.01 -   0.01 -   0.01 -   RAJ BANGLE HOUSE VALSAD 0.33 0.25 0.46 0.46 0.54 0.49 SHAH & SONS-VAPI VAPI 0.41 -   0.56 0.25 0.67 0.51 (F & F DONE) PARTH ENTERPRISE VARACHHA 0.03 -   0.04 -   0.05 -   MADHAV ENTERPRISES VERAUAL 0.01 -   0.02 -   0.02 -  

Explanation / Answer

To count unique in Column B

=SUMPRODUCT((B3:B100<>"")/COUNTIF(B3:B100,B3:B100&""))

Count unique in column B where there is a number in column C. ARRAY enter the formula then drag right for column D.

=COUNT(1/FREQUENCY(IF(ISNUMBER(C$1:C$100),IF($B$1:$B$100<>"",MATCH($B$1:$B$100,$B$1:$B$100,0))),ROW($B$1:$B$100)-ROW($B$1)+1))

This is an array formula which must be enterred by pressinsg CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourseelf. If you edit the formula
you must enter it again with CTRL+Shift+Enteer.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.