Results 1 to 3 of 3

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

  1. #1

    How to count the number of customers without deposit accounts



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #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. #3
    Quote Originally Posted by alfred View Post
    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 ) )

Posting Permissions

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