# Thread: How to count the number of customers without deposit accounts

1. ## How to count the number of customers without deposit accounts

Hi Ken

I have a lookup table that contains customer info and a data table that contains deposit account details (including foreign key). These tables are related via the customer id key.

How do I count the number of customers that do not have a deposit account? I understand that if the parent table (i.e. Customer lookup table) row has no corresponding row in the child table (i.e. deposit table), it throws up a blank.

So, after creating this base measure, [No. of customers]:=counta(Customertble[CustomerID]), how can I use this to count the blanks in the deposit data table (i.e. CustID that do not have any corresponding rows in the deposit data table)?

Alfred

2. Paste this code into a Routine Module and call with a Command Button :

Code:
Option Explicit

Sub CountBlanks()
'Updateby20140310
Dim rng As Range
Dim WorkRng As Range
Dim total As Long
On Error Resume Next

Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", "", WorkRng.Address, Type:=8)
For Each rng In WorkRng
If IsEmpty(rng.Value) Then
total = total + 1
End If
Next
MsgBox "There are " & total & " blank cells in this range."
End Sub
You can also use this formula, editing the targeted range :

Code:
=COUNTBLANK(A1:A20)

3. Originally Posted by alfred
Hi Ken

I have a lookup table that contains customer info and a data table that contains deposit account details (including foreign key). These tables are related via the customer id key.

How do I count the number of customers that do not have a deposit account? I understand that if the parent table (i.e. Customer lookup table) row has no corresponding row in the child table (i.e. deposit table), it throws up a blank.

So, after creating this base measure, [No. of customers]:=counta(Customertble[CustomerID]), how can I use this to count the blanks in the deposit data table (i.e. CustID that do not have any corresponding rows in the deposit data table)?

Alfred
You could try:

Customers Without Deposit Account := COUNTROWS ( FILTER ( Customers, COUNTROWS ( RELATEDTABLE ( DeposotAccounts ) ) = 0 ) )

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•