Forum Discussion
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 cla...
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.