Need help Excel Formula

Question

I need help to compose a formula in excel. Basically picture below explain what i need, this is just a sample. In column E is many different entry. From time to time I need to SUM in column G from some cells column E, depending of position of No."1" in column B. No."1" is always a mark when I need to SUM, and the distance of No. "1" can varied.

Or it can be as per next picture. In Column A is "date and Time" at 12:00 need SUM from cells in column E to previous 12:00 in column A. Hope that is clear what I need.

I Can't group it, because I don't know how many cells will be in column B form 1 to 1, or in column A from 12:00 to 12:00. The cells between the values varied. — Silvano S., Jun 23 '18 at 22:14
It can be I'm not that familiar with that function. Please give how I can use it in my case. — Silvano S., Jun 23 '18 at 22:20
@StephenRauch &SilvanoS. Actually this is not a duplicate of [How can I "group by" and sum a column in excel?](//superuser.com/q/405555). There is nothing to "group by" in this question. None of the answers in the proposed duplicate target can be directly applied to this question as it stands. This question is about summing a number of cells based on the values of another column being in a certain range, *not* summing cells where some other column has the *same* value. I'm not surprised that Silvano is confused by being told to "group" the dates. Seriously, people! — robinCTS, Jun 24 '18 at 18:00
@robinCTS, you can groupby the Date, or some modulus there of. — Stephen Rauch, Jun 24 '18 at 18:14
@StephenRauch Even if you can, that is not explained in any of the proposed duplicate answers. I'm not aware of how to do so. None of the answers help me to work it out. How is a newbie expected to use that duplicate target question/answers to solve the problem? What do you mean exactly by "some modulus there of"? How would you apply that to this problem? Besides, grouping by the Date won't work, as the problem requires using the time as well. (I presume you actually meant date and not datetime. If you meant datetime, that's even worse as they are all unique.) — robinCTS, Jun 24 '18 at 18:35
@SilvanoS.: If you asked the wrong question (or you asked the question wrong), don’t just post a comment (and especially don’t just post a comment *on an answer*); [edit] your question to make it clearer and more complete. — Scott - Слава Україні, Jun 30 '18 at 21:21

robinCTS · Accepted Answer · 2018-06-25T15:59:24.733

3

The easiest solution is for the first scenario, i.e. the one using helper columnB:

Enter the following formula in G4 and ctrl-enter/copy-paste/fill-down the column:

=IF(B4="","",SUM(INDEX(E$3:E4,1+IFERROR(MATCH(1,B$3:B3,1),1)):E4))

Note that the OR(ROW()=ROW(B$3)+1,…) part is required to avoid an error in the first cell of the column (cell G4).

The formula for the second scenario is slightly more complicated :

Enter the following formula in G4 and ctrl-enter/copy-paste/fill-down the column:

=IF(A4-INT(A4)<>0.5,"",SUM(INDEX(E:E,1+LARGE(INDEX((A$3:A3-INT(A$3:A3)=0.5)*(ROW(A$3:A3)),),1)):E4))

edited Jun 25 '18 at 15:59

answered Jun 24 '18 at 05:06

robinCTS

4,327
4
20
29

@RubinCTS thanks for help. Second formula working fine, just I make a mistake during explaining. SUM of the value should be G6=E6+E5; G8=E8+E7; G12=SUM(E12:E9): G18=SUM(E18:E13) ecc. ecc. again depending where is 12:00 in column "A", but without the previous 12:00. I was trying to correct, but this formula is far to advance for me. – Silvano S. Jun 24 '18 at 18:39
1

@SilvanoS. OK. I'll fix it and update the answer. EDIT: All done. Answer has been updated. – robinCTS Jun 24 '18 at 19:05
1

@RubinCT Thanks it works perfect, just what I needed. :-) – Silvano S. Jun 24 '18 at 20:15

wilson · Answer 2 · 2018-06-27T03:39:01.390

Method 1: Using conditional formatting (No helper column)

Input into G5, and copy down to G6:G18

=IF(B4=1, E4, G4+E4)              <-- for the easy scenario

=IF(MOD(A4, 1)=0.5, E4, G4+E4)    <-- for the complicated scenario

Conditional formatting
- Highlight G5:G18
- Home > Conditional Formatting > New Rule
- Use a formula to determine which cells formats
- Formula: =B5<>1
- Format: font colour = white (to hide the unnecessary values)

Method 2: Using helper column

Input into H5, and copy down to H6:H18

=IF(B4=1, E4, H4+E4)             <-- for the easy scenario

=IF(MOD(A4, 1)=0.5, E4, H4+E4)   <-- for the complicated scenario

Input into G5, and copy down to G6:G18
```
=IF(B5=1, H5, "")
```

As date/time is stored in Excel as decimal value,

20/06/2018 00:00:00 = 43271
20/06/2018 12:00:00 = 43271.5

We can use MOD to check if the time is 12:00.

Downvote is not from me, however I agree your answer is confusing. Both cases you include two formulas to be included in the same cell (??). Also some screenshot of the solution might help to understand it. — Máté Juhász, Jun 27 '18 at 05:07

Need help Excel Formula

2 Answers2

Method 1: Using conditional formatting (No helper column)

Method 2: Using helper column