Forum Discussion
Solved
Disregard Rows in Total Calculation that have >4 Duplicates
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,...
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)
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)
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.
- look to see if the total of the family's combined medical bills is greater than the family deductible ($2,000).
Is this possible?? I've updated the file you sent me with this additional info. Any help you can give is greatly appreciated!!