How to count the number of customers without deposit accounts

alfred

New member
Joined
Mar 16, 2017
Messages
6
Reaction score
0
Points
0
Hi Ken

Kindly seek your assistance please.

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

Kindly seek your assistance please.

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