Community Hubs

- 663K Members
- 9,957 Online
- 816K Conversations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: Excel formula help

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 10:09 AM

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 shot attached.

Labels:

21 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 11:18 AM

That could be

=SUM(D2:CHOOSE(<skip H2?>+1,I2,G2))

where <skip H2?> is formula/value for your logical condition

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 11:32 AM

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).

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 11:48 AM

could you please clarify, if

H2=0.5

H2=1

what it'll be in such cases

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 11:57 AM

Hello,

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

they intersect!

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 12:08 PM

H2 will always be a whole number. If H2=1 (or greater), then C2 should =SUM(D2:H2).

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 12:24 PM

in this case you can try this: =IF(H2>0,SUM(D2:H2),D2+F2+G2+I2)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 12:25 PM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 01:23 PM

Oops, I spoke too soon. It still doesn't work. See screenshot attached. Test Student Option 2 should be a 100. The next row down is a real world (not perfect) score and should be an 82 but it's scoring out at 74.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 01:31 PM

I know this stuff is way beyond my basic skill set, but why would I want to subtract the value from E2?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 01:36 PM

Since based on your requirement if H2=0 you sum

@prr924 wrote:

then C2 =SUM(D2,F2,G2,I2).

other words all from D2 to I2 but E2 and H2.

H2 is zero under this condition, thus you need to substruct only E2 from full sum.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 01:43 PM

I skipped your latest sample, it looks like you sum D2:I2 independently of H2. What is your exact logic?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 02:38 PM

If I use this formula

=IF(H2>0,SUM(D2:H2),SUM(D2:I2))

that gives a total score of 100 for all three rows.

This is an assignment that has two options. Students can either write a paper on a finished research project (option 1: scored in column H) or they can write a paper presenting 2 hypotheses and a research plan (option 2: scored in columns J-L which are then summed in column I). They will have a score in column H or column I, never both.

All students, regardless of which option they choose, will have some points in columns D-G that need to be included in their total score.

Maybe this just can't be done?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 02:51 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 02:53 PM

If only one or the other of H2 or I2 has a non-zero value, i.e., a student could't have marks in both Option 1 and Option 2, then just using the formula

=SUM(D2:I2)

should always work.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019 02:55 PM

Ahmad, you rock!! Thank you so much. Wow, that's so elegantly simple but I could not figure it out for myself.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Share

by little2fern on May 29, 2020

by JonasVH on May 29, 2020

by CBHJoshD on May 28, 2020

by resham1985 on May 28, 2020

by cuong on May 27, 2020

by cuong on May 20, 2020

by Ingeborg Hawighorst on May 13, 2020

Posted in Microsoft Ignite The Tour 2019 on February 14, 2020

Microsoft Store

Education

Developer