Search code examples
excel-formulavlookupsumifs

How to sum up values in excel based on two different conditions?

Excel Columns

I have the following data in excel. As you can see above each ArchID/ConopsID is linked to different FY and in each FY there are different tiers. I am trying to find a way where if tier is 1 or 2, the value stays the same. However, if you see ArchID = 700 only goes up to tier 3 whereas ArchID = 300 goes to tier 4. I want if an ArchID only goes up to tier 3, we take the value shown on tier 3 but if tier 4 is present, then we add up (sum) the value of tier 3 and 4 and insert that as the new value.

For example, for ArchID = 322, in FY 2022, the new value displayed in tier 3 should be 8 (2 + 6). Where as for ArchID = 700, in FY 2022, the value displayed in tier 3 would stay at 5 since there is no tier 4.

Please let me know how I can solve this.

Solution

  • Here is a bit of a lengthy function (given you've provided values that depend upon several requirements/fields - e.g. Arch/Conops,FY & Tier) that works:

    =SUM(($F$4:$F$12)*($B$4:$B$12=B4)*($D$4:$D$12=D4)*($C$4:$C$12=C4)*(IF($E$4:$E$12>3,3,$E$4:$E$12)=MIN(E4,3)))

    New value function

    This assumes you want to return the same 'tier 3+4 sum' for tier 4 too -- else just reference E4 instead of min(E4,3) at the end of this function (which will return 0 instead), or simply put a big IF around whole function above (e.g. conceptually: IF(Tier>3, "n/a", function above) - but you can customize as req./desired).

    Also - function above will actually sum numbers corresponding to any tier greater than 3 (5,6, ...) if these exist - assuming not an issue but if so modify above function as follows:

    =SUM(($F$4:$F$12)*($B$4:$B$12=B4)*($D$4:$D$12=D4)*($C$4:$C$12=C4)*(IF(($E$4:$E$12>3)*($E$4:$E$12<5),3,$E$4:$E$12)= E4))

    (being lazy by putting E4 at the end, which will return 0 for Tier 4, but you can customize using similar if statement in place of E4/cutomize per above as req.)