- 595K Members
- 5,797 Online
- 724K 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

Related Conversations

quiero almacenar los resultados de la formula contar si cuando recalculo con F9 en celdas diferentes

manu_agus605
in
Excel
on
04-08-2020
20
Views

0 Likes

0 Replies

Excel operates very slowly and stops responding constantly, how to fix?

Justin_R
in
Excel
on
04-08-2020
41
Views

0 Likes

2 Replies

EXCEL 365 Privacy Setting stopped Excel from making PDFs and Printing

Nathan2591
in
Excel
on
04-08-2020
24
Views

0 Likes

0 Replies

Share

Microsoft Store

Education

Developer