2 Replies Latest reply on Nov 3, 2016 11:18 PM by Norbert Maijoor

# Grouping based on which columns are populated.

Hello,

I have a list of people, along with their Home/Cell/Business phone numbers, as follows in this example (I can't seem to type after the table, so the table example will be at the bottom of my post.)

I want a graph that shows:

People with no phone numbers: 2

People with all phone numbers: 1

People with only home phone numbers: 0

People with only cell phone numbers: 0

People with only business phone numbers: 0

People with only a home phone and cell phone: 1

People with only a cell phone and business phone: 2

People with only a home phone and business phone: 0

I basically want to be able to see a count of non-null fields for every phone combination.

Thank you.

Client IDClient NameHome PhoneCell PhoneBusiness Phone
1John555123456755598734245553426443
2JaneNULLNULLNULL
3DerekNULL55578442355557364786
4BillNULLNULLNULL
5

Sarah

NULL55543334215553423631
6Mark55556423425553423462NULL
• ###### 1. Re: Grouping based on which columns are populated.

Hi.

Here is one way of doing it:

1. Create one new field for each phone type:

2. Concatenate them like this:

3. Result:

• ###### 2. Re: Grouping based on which columns are populated.

Hi William,

Find my approach as reference below and stored in attached workbook version 9.3

Calculatore:

If    [COUNTD([Business Phone]])]+      [COUNTD([Cell Phone]])]+      [COUNTD([Home Phone]])]=0 then 2

elseif [COUNTD([Business Phone]])]+      [COUNTD([Cell Phone]])]+      [COUNTD([Home Phone]])]=3 then 1

elseif [COUNTD([Business Phone]])]=0 and [COUNTD([Cell Phone]])]=0 and [COUNTD([Home Phone]])]=1 then 0

elseif [COUNTD([Business Phone]])]=0 and [COUNTD([Cell Phone]])]=1 and [COUNTD([Home Phone]])]=0 then 0

elseif [COUNTD([Business Phone]])]=1 and [COUNTD([Cell Phone]])]=0 and [COUNTD([Home Phone]])]=0 then 0

elseif [COUNTD([Business Phone]])]=0 and [COUNTD([Cell Phone]])]=1 and [COUNTD([Home Phone]])]=1 then 1

elseif [COUNTD([Business Phone]])]=1 and [COUNTD([Cell Phone]])]=1 and [COUNTD([Home Phone]])]=0 then 2

elseif [COUNTD([Business Phone]])]=1 and [COUNTD([Cell Phone]])]=0 and [COUNTD([Home Phone]])]=1 then 0 END