Count unique cells based on column of blank cells

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
368
Office Version
  1. 365
Platform
  1. Windows
Hi,

I've managed to get part of my formula to work which counts the number of unique records in column B. I only need it to run a calculation on those cells in G which are blank. Any ideas? Formula is in L5727 as i was testing it there.

2024 Woodford Proactive Calls.xls
ABCDEFGHIJKL
572615/03/241CS22555WOAlina MariaSales-iYesAlina345382
572715/03/241CS22555WOAlina MariaSales-iYesAlina345382910
572815/03/241CS22569WOAlina MariaSales-iYesAlina345382
572915/03/241CS22569WOAlina MariaSales-iYesAlina345382
573015/03/241CS22581WOAlina MariaSales-iYesAlina345382
573115/03/241CS22581WOAlina MariaSales-iYesAlina345382
573215/03/241CS22590WOAlina MariaSales-iYesAlina345382
573315/03/241CS22590WOAlina MariaSales-iYesAlina345382
573415/03/241CS22592WOAlina MariaSales-iYesAlina345382
573515/03/241CS22592WOAlina MariaSales-iYesAlina345382
573615/03/241CS22596WOAlina MariaSales-iYesAlina345382
573715/03/241CS22596WOAlina MariaSales-iYesAlina345382
573815/03/241CS22607WOAlina MariaSales-iYes345382
573915/03/241CS22607WOAlina MariaSales-iYes345382
574015/03/241CS22613WOAlina MariaSales-iYes345382
574115/03/241CS22613WOAlina MariaSales-iYes345382
574215/03/241CS22624WOAlina MariaSales-iYes345382
574315/03/241CS22624WOAlina MariaSales-iYes345382
574415/03/241CS22630WOAlina MariaSales-iYes345382
574515/03/241CS22630WOAlina MariaSales-iYes345382
YTD_Figures
Cell Formulas
RangeFormula
H5726:H5745H5726=IF(I5726=45322,1,IF(I5726=45351,2,IF(I5726=45382,3,0)))
I5726:I5745I5726=EOMONTH(A5726,0)
L5727L5727=SUM(IF(FREQUENCY(IF(LEN(B2:B5789)>0,MATCH(B2:B5789,B2:B5789,0),""),IF(LEN(B2:B5789)>0,MATCH(B2:B5789,B2:B5789,0),""))>0,1))
G5726:G5737G5726=VLOOKUP(B5726,WO_Bronze!$B$2:$J$947,9,FALSE)
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
If I am understanding correctly, try this instead of the formula you currently have in L5727
Excel Formula:
=LET(r,B2:B5789,ROWS(UNIQUE(FILTER(r,r<>""))))

For the added condition, try
Excel Formula:
=LET(r,B2:B5789,IFNA(ROWS(UNIQUE(FILTER(r,(r<>"")*(G2:G5789=""),NA()))),0))
 
Upvote 0
If I am understanding correctly, try this instead of the formula you currently have in L5727
Excel Formula:
=LET(r,B2:B5789,ROWS(UNIQUE(FILTER(r,r<>""))))

For the added condition, try
Excel Formula:
=LET(r,B2:B5789,IFNA(ROWS(UNIQUE(FILTER(r,(r<>"")*(G2:G5789=""),NA()))),0))
Peter, that's amazing and thank you so much for your wisdom. Works perfectly.
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
Hi Peter,
Sorry, me again. Column E will only ever contain 3 names "Sales Order", "Sales Quote" and "Sales-i". Is there a way to use the formula and adapt it so it looks how many transactions there are in column B (your formula: =LET(r,B2:B5789,ROWS(UNIQUE(FILTER(r,r<>"")))) and count how many say "Sales Order", "Sales Quote" or "Sales-i".
Or possibly the number of unique customers (your formula: =LET(r,B2:B5789,ROWS(UNIQUE(FILTER(r,r<>"")))) and the same number of Sales Order", "Sales Quote" or "Sales-i". .

Here's what the sheet looks like now with the table being populated:

15/03/241CS22555WOAlina MariaSales-iYesAlina345382
15/03/241CS22555WOAlina MariaSales-iYesAlina345382
15/03/241CS22569WOAlina MariaSales-iYesAlina345382
15/03/241CS22569WOAlina MariaSales-iYesAlina345382
15/03/241CS22581WOAlina MariaSales-iYesAlina345382GOLD - SILVER ACCOUNTS CONTACTED
15/03/241CS22581WOAlina MariaSales-iYesAlina345382TransactionsQ1 CustomersSales GeneratedQuotes IssuedContact Only
15/03/241CS22590WOAlina MariaSales-iYesAlina345382910494
15/03/241CS22590WOAlina MariaSales-iYesAlina345382
15/03/241CS22592WOAlina MariaSales-iYesAlina345382TransactionsTotal number of Gold & Silver Sales Orders, Quotes or Sales-i entries.
15/03/241CS22592WOAlina MariaSales-iYesAlina345382Q1 CustomersTotal number of Gold & Silver accounts with entries
15/03/241CS22596WOAlina MariaSales-iYesAlina345382Sales OrdersTotal number of Gold & Silver Sales Orders
15/03/241CS22596WOAlina MariaSales-iYesAlina345382Quotes IssuedTotal number of Gold & Silver Quotes issued
15/03/241CS22607WOAlina MariaSales-iYes345382Contact OnlyTotal number of Gold & Silver Entries in Sales-i
15/03/241CS22607WOAlina MariaSales-iYes345382
15/03/241CS22613WOAlina MariaSales-iYes345382
15/03/241CS22613WOAlina MariaSales-iYes345382
15/03/241CS22624WOAlina MariaSales-iYes345382
15/03/241CS22624WOAlina MariaSales-iYes345382
15/03/241CS22630WOAlina MariaSales-iYes345382
15/03/241CS22630WOAlina MariaSales-iYes345382
 
Upvote 0
I'm not understanding. Could you make up a small set of dummy data (say 20-30 rows), enter the results for that small sample data manually, post it all (including headings & results table) with XL2BB and explain again in relation to that sample data and results? Part of that explanation might include what "Gold & Silver" has to do with anything as I have no idea.

Also, when using XL2BB please use Mini Sheet not Table Only so we can see row numbers, column headings etc.
 
Upvote 0
I'm not understanding. Could you make up a small set of dummy data (say 20-30 rows), enter the results for that small sample data manually, post it all (including headings & results table) with XL2BB and explain again in relation to that sample data and results? Part of that explanation might include what "Gold & Silver" has to do with anything as I have no idea.

Also, when using XL2BB please use Mini Sheet not Table Only so we can see row numbers, column headings etc.
Morning Peter,
Sorry, i should have made it clearer. I've included a table showing how the date should look and also where the data comes from. What i'm trying to achieve is something to do what you've already sent me (if cells in column G are blank, calculate how many unique account numbers relate to those cells in column B).
Now what i'm looking for is the same but only give results based on the 3 choices in column E which will only ever be "Sales Order", "Sales Quote" and Sales-i".

You'd probably change all my formulas but i've built it with what limited knowledge i have.

2024 Woodford Proactive Calls-1.xls
IJKLM
1GOLD - SILVER ACCOUNTS CONTACTED
2TransactionsQ1 CustomersSales GeneratedQuotes IssuedContact Only
345354942940982613
Statistics
Cell Formulas
RangeFormula
J3J3=LET(r,YTD_Figures!B2:B5789,IFNA(ROWS(UNIQUE(FILTER(r,(r<>"")*(YTD_Figures!G2:G5789=""),NA()))),0))


2024 Woodford Proactive Calls-1.xls
ABCDEFGHI
1Call DateAccount NumberOwning Branch CodeSales Rep CodeMethodContactedUserMonth
202/01/241TAP1174WONick BarkerSales OrderYes145322
302/01/241TAP1083WONick BarkerSales OrderYes145322
402/01/241TAP1126WONick BarkerSales OrderYes145322
502/01/241TAP1001WONick BarkerSales OrderYes145322
602/01/241TAP1001WONick BarkerSales OrderYes145322
802/01/241BUI019WONick ButcherSales OrderYes145322
902/01/241CAS043RWOWoodford Trade CtrSales OrderYes145322
1002/01/241BUI019WONick ButcherSales OrderYes145322
1102/01/241CAR154WONick BarkerSales OrderYes145322
1202/01/241CAS043RWOWoodford Trade CtrSales OrderYes145322
1302/01/241BUI019WONick ButcherSales OrderYes145322
1502/01/241BUI019WONick ButcherSales OrderYes145322
1602/01/241ONE009WONick ButcherSales OrderYes145322
1702/01/241ECJ001WOJo Amos SESales OrderYes145322
1802/01/241WYC004WONick ButcherSales OrderYes145322
1902/01/241YES001WONick ButcherSales OrderYes145322
2002/01/241CAR154WONick BarkerSales OrderYes145322
2102/01/241CAR154WONick BarkerSales OrderYes145322
2202/01/241CAS043RWOWoodford Trade CtrSales OrderYes145322
2302/01/241TOP619WOSteve WarsapSales OrderYes145322
2402/01/241ONE009WONick ButcherSales OrderYes145322
2502/01/241CAR154WONick BarkerSales OrderYes145322
2602/01/241BUI019WONick ButcherSales OrderYes145322
2702/01/241BAL003WOJo Amos TKSales OrderYes145322
2802/01/241CAS043RWOWoodford Trade CtrSales OrderYes145322
2902/01/241BUI019WONick ButcherSales OrderYes145322
3002/01/241BUI019WONick ButcherSales OrderYes145322
3102/01/241COS004WONick BarkerSales OrderYes145322
3202/01/241COS004WONick BarkerSales OrderYes145322
3302/01/241COS004WONick BarkerSales OrderYes145322
3402/01/241CS12821WONick ButcherSales OrderYes145322
3502/01/241CS28273WOAlina MariaSales OrderYes145322
3602/01/241COS004WONick BarkerSales OrderYes145322
3702/01/241COS004WONick BarkerSales OrderYes145322
3802/01/241COS004WONick BarkerSales OrderYes145322
3902/01/241COS004WONick BarkerSales OrderYes145322
4002/01/241COS004WONick BarkerSales OrderYes145322
4102/01/241COS004WONick BarkerSales OrderYes145322
4202/01/241COS004WONick BarkerSales OrderYes145322
4302/01/241COS004WONick BarkerSales OrderYes145322
4502/01/241COS004WONick BarkerSales OrderYes145322
4602/01/241CS2922WONick ButcherSales OrderYes145322
4702/01/241COS004WONick BarkerSales OrderYes145322
4802/01/241COS004WONick BarkerSales OrderYes145322
4902/01/241CS11288WONick ButcherSales OrderYes145322
5002/01/241COS004WONick BarkerSales OrderYes145322
5102/01/241COS004WONick BarkerSales OrderYes145322
5202/01/241COS004WONick BarkerSales OrderYes145322
5302/01/241COS004WONick BarkerSales OrderYes145322
5402/01/241MOD010WOJo Amos TKSales OrderYes145322
5502/01/241COS004WONick BarkerSales OrderYes145322
5602/01/241ONE009WONick ButcherSales OrderYes145322
5702/01/241CAS043RWOWoodford Trade CtrSales OrderYes145322
5802/01/241CAS043RWOWoodford Trade CtrSales OrderYes145322
5902/01/241COS004WONick BarkerSales OrderYes145322
6002/01/241COS004WONick BarkerSales OrderYes145322
6102/01/241BUI019WONick ButcherSales OrderYes145322
6202/01/241COS004WONick BarkerSales OrderYes145322
6302/01/241CAS043RWOWoodford Trade CtrSales OrderYes145322
6402/01/241CAS043RWOWoodford Trade CtrSales OrderYes145322
6502/01/241CAS043TWOWoodford Trade CtrSales OrderYes145322
6602/01/241ONE009WONick ButcherSales OrderYes145322
6702/01/241IFI001WONick ButcherSales OrderYes145322
6802/01/241WRE005WONick ButcherSales OrderYes145322
6902/01/241CS8183WOJo Amos SESales OrderYes145322
7002/01/241CS12821WONick ButcherSales OrderYes145322
7102/01/241CAS043RWOWoodford Trade CtrSales OrderYes145322
7302/01/241YES001WONick ButcherSales OrderYes145322
7402/01/241LLK001WOJamie BattSales OrderYes145322
7502/01/241CS2922WONick ButcherSales OrderYes145322
7602/01/241GEN024WOJo Amos TKSales OrderYes145322
7802/01/241JBL003WOJo Amos SESales OrderYes145322
7902/01/241JAC020WONick ButcherSales OrderYes145322
8002/01/241BAT104WONick ButcherSales OrderYes145322
8102/01/241LLK001WOJamie BattSales OrderYes145322
8202/01/241LLK001WOJamie BattSales OrderYes145322
8302/01/241JAC020WONick ButcherSales OrderYes145322
8402/01/241CS11163WOAlan BeynonSales OrderYes145322
8602/01/243JEW562WONick ButcherSales QuoteYes145322
8702/01/241GRI004WOJo Amos SESales QuoteYes145322
8802/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
8902/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
9102/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
9202/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
9302/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
9402/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
9502/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
9602/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
9702/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
9802/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
10002/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
10102/01/241WRE005WONick ButcherSales QuoteYes145322
10202/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
10302/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
10402/01/241HUW242WOAlan BeynonSales QuoteYes145322
10702/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
11002/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
11102/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
11202/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
11402/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
11502/01/241CAS043WOWoodford Trade CtrSales QuoteYes145322
11602/01/241CAS043TWOWoodford Trade CtrSales QuoteYes145322
11702/01/241CS11163WOAlan BeynonSales QuoteYes145322
11802/01/241CS27862WOPaul HollingsheadSales QuoteYes145322
11902/01/241CS22678WOPaul HollingsheadSales QuoteYes145322
12002/01/241CS22678WOPaul HollingsheadSales QuoteYes145322
12102/01/241CS22678WOPaul HollingsheadSales QuoteYes145322
12202/01/241CS22678WOPaul HollingsheadSales QuoteYes145322
12303/01/241TAP1051WONick BarkerSales OrderYes145322
12403/01/241TAP1049WONick BarkerSales OrderYes145322
12503/01/241TAP1201WONick BarkerSales OrderYes145322
12603/01/241TAP1133WONick BarkerSales OrderYes145322
12703/01/241TAP1051WONick BarkerSales OrderYes145322
12803/01/241ROO013WONick ButcherSales OrderYes145322
12903/01/241ROO013WONick ButcherSales OrderYes145322
13003/01/241CAR154WONick BarkerSales OrderYes145322
13203/01/241COS004WONick BarkerSales OrderYes145322
13403/01/241CAS043RWOWoodford Trade CtrSales OrderYes145322
13503/01/241COS004WONick BarkerSales OrderYes145322
13603/01/241CAS043RWOWoodford Trade CtrSales OrderYes145322
13703/01/241CAR154WONick BarkerSales OrderYes145322
13803/01/241MOD010WOJo Amos TKSales OrderYes145322
13903/01/241BUI019WONick ButcherSales OrderYes145322
14003/01/241CS12821WONick ButcherSales OrderYes145322
14103/01/241COS004WONick BarkerSales OrderYes145322
14203/01/241COS004WONick BarkerSales OrderYes145322
14303/01/241BUI019WONick ButcherSales OrderYes145322
14403/01/241COS004WONick BarkerSales OrderYes145322
14503/01/241CAS043RWOWoodford Trade CtrSales OrderYes145322
14603/01/241BAL003WOJo Amos TKSales OrderYes145322
14703/01/241COS004WONick BarkerSales OrderYes145322
14803/01/241PRS002WOAlan BeynonSales OrderYes145322
14903/01/241HUW242WOAlan BeynonSales OrderYes145322
15103/01/241BSF001WOJo Amos TKSales OrderYes145322
15203/01/241GAM005WONick ButcherSales OrderYes145322
15403/01/241MBS003WONick ButcherSales OrderYes145322
15503/01/241FIT018WOJo Amos TKSales OrderYes145322
15603/01/241BUI019WONick ButcherSales OrderYes145322
15703/01/241WAS002WOJo Amos TKSales OrderYes145322
15803/01/241BUR031WOJo Amos SESales OrderYes145322
16003/01/241WAR037WONick ButcherSales OrderYes145322
16103/01/241CAS043RWOWoodford Trade CtrSales OrderYes145322
16203/01/241WAS002WOJo Amos TKSales OrderYes145322
16303/01/241CS11288WONick ButcherSales OrderYes145322
16403/01/241COS004WONick BarkerSales OrderYes145322
16503/01/241COS004WONick BarkerSales OrderYes145322
16603/01/241COS004WONick BarkerSales OrderYes145322
16703/01/241COS004WONick BarkerSales OrderYes145322
16803/01/241CS11288WONick ButcherSales OrderYes145322
16903/01/241ONW001WOJo Amos SESales OrderYes145322
17003/01/241ECH003WOJo Amos SESales OrderYes145322
17103/01/241CAS043RWOWoodford Trade CtrSales OrderYes145322
17303/01/241CAS043RWOWoodford Trade CtrSales OrderYes145322
17403/01/241CAS043RWOWoodford Trade CtrSales OrderYes145322
17703/01/241CS15408WOAlan BeynonSales OrderYes145322
17803/01/241MOD010WOJo Amos TKSales OrderYes145322
18003/01/241CAS043RWOWoodford Trade CtrSales OrderYes145322
18103/01/241CON120WOJo Amos SESales OrderYes145322
18203/01/241BUI019WONick ButcherSales OrderYes145322
18403/01/241BUI019WONick ButcherSales OrderYes145322
18503/01/241CLA106WONick ButcherSales OrderYes145322
18603/01/241CS22678WOPaul HollingsheadSales OrderYes145322
18803/01/243JEW562WONick ButcherSales QuoteYes145322
19003/01/241FIT018WOJo Amos TKSales QuoteYes145322
19103/01/241ONE009WONick ButcherSales QuoteYes145322
19203/01/241FIT030WOJo Amos TKSales QuoteYes145322
19303/01/241PRS002WOAlan BeynonSales QuoteYes145322
19403/01/241CAS043TWOWoodford Trade CtrSales QuoteYes145322
19503/01/241MBS003WONick ButcherSales QuoteYes145322
19603/01/241BUI019WONick ButcherSales QuoteYes145322
19803/01/241CS28162WOAlina MariaSales QuoteYes145322
19903/01/241ECH003WOJo Amos SESales QuoteYes145322
20003/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
20103/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
20203/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
20503/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
20603/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
20803/01/241SAN032WOAlan BeynonSales QuoteYes145322
20903/01/241CAS043RWOWoodford Trade CtrSales QuoteYes145322
21003/01/241BUR031WOJo Amos SESales-iYes145322
21103/01/241CS28301WOAlina MariaSales-iYes145322
21203/01/241BUR031WOJo Amos SESales-iYes145322
21303/01/241GRI004WOJo Amos SESales-iYes145322
21704/01/241TAP1122WONick BarkerSales OrderYes145322
21804/01/241TAP1083WONick BarkerSales OrderYes145322
21904/01/241TAP1122WONick BarkerSales OrderYes145322
22104/01/241ONE009WONick ButcherSales OrderYes145322
22204/01/241BEN3844WOAlan BeynonSales OrderYes145322
YTD_Figures
Cell Formulas
RangeFormula
H2:H6,H8:H13,H15:H43,H45:H71,H73:H76,H78:H84,H86:H89,H91:H98,H100:H104,H107,H110:H112,H114:H130,H132,H134:H149,H151:H152,H154:H158,H160:H171,H173:H174,H177:H178,H180:H182,H184:H186,H188,H190:H196,H198:H202,H205:H206,H208:H213,H217:H219,H221:H222H2=IF(I2=45322,1,IF(I2=45351,2,IF(I2=45382,3,0)))
I2:I6,I8:I13,I15:I43,I45:I71,I73:I76,I78:I84,I86:I89,I91:I98,I100:I104,I107,I110:I112,I114:I130,I132,I134:I149,I151:I152,I154:I158,I160:I171,I173:I174,I177:I178,I180:I182,I184:I186,I188,I190:I196,I198:I202,I205:I206,I208:I213,I217:I219,I221:I222I2=EOMONTH(A2,0)
 
Upvote 0
I'm not understanding. Could you make up a small set of dummy data (say 20-30 rows), enter the results for that small sample data manually, post it all (including headings & results table) with XL2BB and explain again in relation to that sample data and results? Part of that explanation might include what "Gold & Silver" has to do with anything as I have no idea.

Also, when using XL2BB please use Mini Sheet not Table Only so we can see row numbers, column headings etc.
If you want the workbook let me know and i'll email.
 
Upvote 0
If you want the workbook let me know and i'll email.
That is not permitted - see #4 of the Forum Rules regarding taking questions off-forum.

Could you make up a small set of dummy data (say 20-30 rows), ....
Your sample is hardly 20-30 rows and the results shown do not seem to correspond to the data that is shown. For example Q1 customers in your sheet shows 494 whereas for the sample data it appears to be <100.

Could you make up a small set of dummy data (say 20-30 rows), enter the results for that small sample data manually, post it all (including headings & results table) with XL2BB and explain again in relation to that sample data and results?
 
Upvote 0
Please do not mark a post that doesn't contain a solution. I have removed the solution mark from post #9 as it certainly doesn't contain a solution to your question. :)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

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

Which adblocker are you using?

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
Back
Top