Disregard Rows in Total Calculation that have >4 Duplicates

[bryson8420]

I am building a spreadsheet to calculate projected revenue for a service based on calculation per person. Cost per person is based on age brackets, so I have a table for this to use vlookup. However, I would like to limit the amount of paying people to 4 per family, so if we have a family of 6, I will only count the first 4 members of the family.

Each of our members is assigned a 6-digit family number and a 2-digit person number, so I have an export from our system that lists each person in a row with their family number, person number, and the age bracket they're in.

Is there a way to disregard (not count) the rows that include any family member >4?

Here's the table with the cost per age bracket:

Here's the table that lists each person in a row with their Family number, Person number, and their age bracket, with the "Cost" column using a vlookup to the "Cost per Age Bracket" table. Highlighted in red are people >4 in a family (person 05 and person 06).

Is it possible to total the "Cost" column, but NOT count any of the rows >4 per family?

[#2]

@bryson8420 Why not use an IF formula for Cost that sets the cost to zero when the person number is either 05 or 06? The you don't have to exclude them from the total.

[#3 · Accepted solution]

@bryson8420 You achieve your result by two way. You can use SUMIFS() to get total cost by applying condition <=4. Try-

=SUMIFS(D2:D20,B2:B20,"<=4")

Or apply a condition when you are looking up values against age. Try-

=IF(COUNTIFS($A$2:$A2,A2)>4,0,XLOOKUP(C2,$H$2:$H$13,$I$2:$I$13))

 Or condition used to person number.

=IF(B2<=4,XLOOKUP(C2,$H$2:$H$13,$I$2:$I$13),0)

[#4]

@Harun24HR Thanks for your help!! I do see that either of them will work, but I am going to use the second method you proposed so I can replace the hard-coded "4" in the if statement to reference a certain cell value so we can change that cell value to manipulate the calculations:

• 

There's a second item I can't figure out, and was thinking I may be able to figure it out with the answer to my first item, but I still can't quite work it out:

Our services are similar to an health insurance company, where each person has a deductible before we begin paying on their medical bills. However, we have an individual deductible (per person), but then also have a family deductible, which is typically 2x the individual deductible. Once one of the deductibles is met, we will begin paying out a certain percentage of medical bills, say 90%. We want to be able to manipulate the numbers for individual deductible, family deductible, and percent of payout to show projected costs.

For example, the individual deductible is $1,000 and the family deductible is $2,000.

• So in one family, if person #1 has medical bills over the individual deductible of $1,000, we will pay 90% on their losses after the deductible. If person #2 has medical bills over their individual deductible of $1,000, we will begin paying 90% on their medical bills after the deductible, and 90% on all the other family members' medical bills as well, because the family deductible of $2,000 was met.
• This is also aggregate, so if 4 of the family members each have medical bills of $500, this would equal the $2,000 family deductible, so we would begin paying 90% on all family members.

So the formula needs to look at how many people in the family:

• If there's just one person, it will return the amount of medical bills, less individual deductible ($1,000), multiplied by percentage of payout (90%).
• If there's two or more people, it will:
  • look to see if the total of the family's combined medical bills is greater than the family deductible ($2,000).
    • If so, it will return the total medical bills of all family members, less family deductible, multiplied by percentage payout (90%).
    • If not, then it will see if any person has medical bills over the individual deductible ($1,000).
      • If so, it will return that person's medical bills, less individual deductible, multiplied by percent of payout (90%). 
      • If not, it will return 0.

Is this possible?? I've updated the file you sent me with this additional info. Any help you can give is greatly appreciated!!