Forum Discussion

Emad Al Assi's avatar
Emad Al Assi
Copper Contributor
Jul 29, 2018

Get counts of multiple cells from multiple columns

Hi All, 

 

I am working as an insurance agent & part of my job is to provide immediate quotations for the client's.

 

I got a sheet from the insurance company contains the rates VS (Insurance class, Age Band, Relationship). as below sample.

Age Band

The client member's data is containing many columns including (Insurance class, Age Band, Relationship). as below sample.

Members Data

My question is how to prepare  a formula that help me to get the exact count of  members considered the above criteria.

 

Best Regards,

Excel
  • Lars Küster's avatar
    Lars Küster
    Copper Contributor
    Jul 31, 2018

    Hi.

     

    First of all I would create a List with the Ageband entries such as

    A;B

    0;0

    2;2

    6;6

    11;11

    16;16

    etc.

     

    This list i would assign an range name such as rngAgeBand

    Then i would create a additional column in your coustomers data named AgeBand and would use vlookup

    H2 = vlookup(g2;rngAgeBand;2;true)

    After that i would create a second additional column Named Status with a if condition to figure out wether its a Employee, Spouse or Children

    I2= if(d2="Employee","Employee",if(or(d2 = "son", d2="daughter","Children","Spouse"))

     

    After this little preparation then i would use the countifa function to figure out which amount of each appears in the data list.

     

    Therefore i would convert the list into a table and name the table like tblRawData

    b5=countifa(tblRawData[AgeBand],value(mid($a5,6,1)),tblData[Status],B$4)

     

    This hasen't been tested within excel. I just wrote the formulas as i thought they should work like this.

     

    Hope this helps you developing your solution

     

     

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jul 31, 2018

    For data as such

    if left table is called Names, the formula to calculate kids (cell J4) will be

    =SUM(COUNTIFS(Names[[Relationship]:[Relationship]],{"Daughter","Son"},Names[[Class]:[Class]],H$2,Names[[Age]:[Age]],"<="&RIGHT($G4,2),Names[[Age]:[Age]],">="&MID($G4,6,2)))

    the rest is similar. Table could be converted to range if for some reasons you prefer them. When the same formula is

    =SUM(COUNTIFS($C$3:$C$31,{"Daughter","Son"},$D$3:$D$31,H$2,$E$3:$E$31,"<="&RIGHT($G4,2),$E$3:$E$31,">="&MID($G4,6,2)))

    and attached. 

Resources