Forum Discussion

bryson8420's avatar
bryson8420
Copper Contributor
May 18, 2023
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,...
excel
Formulas and Functions
  • Harun24HR's avatar
    Harun24HR
    May 19, 2023

    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)

     

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
May 19, 2023

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.

 

Resources