3

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.

Sample picture 1

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.

Sample picture 2

Stephen Rauch
  • 3,296
Silvano S.
  • 33

2 Answers2

3

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

Worksheet Screenshot

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 :

![Worksheet Screenshot][2]

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))
robinCTS
  • 4,407
-1

Method 1: Using conditional formatting (No helper column)

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

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

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

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

wilson
  • 5,222