Forum Discussion

prr924's avatar
prr924
Copper Contributor
Mar 12, 2019

Excel formula help

I'd like to create a formula that either sums columns D2 through H2 OR D2-G2 plus I2. In other words, for the second option I want it to skip H2. Can this be done and if so how do I do it? Screen sho...
excel
Excel on Mac
Formulas and Functions
prr924's avatar
prr924
Copper Contributor
Mar 12, 2019

Sorry, that didn't work. Let me try to explain this better. I want a formula for cell C2 (Total Score) that does this:

IF H2>0, then C2 =SUM(D2:H2).

If H2<1, then C2 =SUM(D2,F2,G2,I2). 

ahmad ali's avatar
ahmad ali
Brass Contributor
Mar 12, 2019

Hello,

I'm sorry but are you  aware that IF H2=0.5 ( or 0.2, ...) the two conditions are accepted ?

they intersect!

 

 

  • prr924's avatar
    prr924
    Copper Contributor
    Mar 12, 2019

    Ahmad, the formula you gave me ALMOST works, but for some reason A student who chooses option 2 comes out at a 90 instead of 100. See attached.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Mar 12, 2019

      Another variant is

      =SUM(D2:I2)-E2*(H2=0)

       

      • prr924's avatar
        prr924
        Copper Contributor
        Mar 12, 2019

        Sergei, I don't want to delete E2--there should always be points in columns D-E, plus points from either H or I (not both). Removing E2 from your formula leaves 

         

        =SUM(D2:I2)*(H2=0) results in a score of 0 for Test Student Option 1 

        =SUM(D3:I3)*(H3=0) results in a score of 100 for Test Student Option 2

         

        Both should be 100. 

Resources