How to Sum a range between cells that have a specific string

L

louisedp

New Member
Joined
Dec 22, 2013
Messages
21
Hi,

In the below Example the value of cell M2: 387 is arrived by using the formula =SUM(L3:L9)
and M10: 418 is arrived by using the formula =SUM(L11:L17)

i.e , Need to sum the integers in-between two "Day-IN"s

Challange: There is more than 1000 records(more than 500 "Day-IN"s) for which I cannot manually use Sum for the cells in-between "Day-IN"s
[TABLE="width: 236"]
<TBODY>[TR]
[TD]1</SPAN>[/TD]
[TD]L </SPAN>[/TD]
[TD]M</SPAN>[/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD]Day-IN[/TD]
[TD="align: right"]387</SPAN>[/TD]
[/TR]
[TR]
[TD]3</SPAN>[/TD]
[TD]112</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4</SPAN>[/TD]
[TD]-65</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]90</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6</SPAN>[/TD]
[TD]-4</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7</SPAN>[/TD]
[TD]115</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8</SPAN>[/TD]
[TD]-47</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9</SPAN>[/TD]
[TD]70</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10</SPAN>[/TD]
[TD]Day-IN</SPAN>[/TD]
[TD="align: right"]418</SPAN>[/TD]
[/TR]
[TR]
[TD]11</SPAN>[/TD]
[TD]128</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12</SPAN>[/TD]
[TD]-56</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13</SPAN>[/TD]
[TD]144</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14</SPAN>[/TD]
[TD]-5</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15</SPAN>[/TD]
[TD]64</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16</SPAN>[/TD]
[TD]-38</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD]82</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18</SPAN>[/TD]
[TD]Day-IN</SPAN>[/TD]
[TD="align: right"]383</SPAN>[/TD]
[/TR]
[TR]
[TD]19</SPAN>[/TD]
[TD]86</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20</SPAN>[/TD]
[TD]-0</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22</SPAN>[/TD]
[TD]-51</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]23</SPAN>[/TD]
[TD]95</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]24</SPAN>[/TD]
[TD]-6</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]25</SPAN>[/TD]
[TD]130</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]26</SPAN>[/TD]
[TD]-56</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]27</SPAN>[/TD]
[TD]71</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]28</SPAN>[/TD]
[TD]Day-IN</SPAN>[/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]


Refer Image : View image: Sum between cells


Someone, Kindly shed some light pls...
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
This is one way :-
But the results do not match Yours ????
Code: 
[COLOR="Navy"]Sub[/COLOR] MG21Jul12
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, sum [COLOR="Navy"]As[/COLOR] Double, Temp [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("L2"), Range("L" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Value = "Day-IN" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not Temp [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] Temp.Offset(, 1) = sum
        [COLOR="Navy"]Set[/COLOR] Temp = Dn
        sum = 0
    [COLOR="Navy"]Else[/COLOR]
        sum = sum + Dn.Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Mick,

Thank you for your reply....

But It would be great if I achieve it using some sort of formula(Index,Reference)...instead of a macro....

MickG said:
This is one way :-
But the results do not match Yours ????
Code: 
[COLOR=navy]Sub[/COLOR] MG21Jul12
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, sum [COLOR=navy]As[/COLOR] Double, Temp [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = Range(Range("L2"), Range("L" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Dn.Value = "Day-IN" [COLOR=navy]Then[/COLOR]
        [COLOR=navy]If[/COLOR] Not Temp [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR] Temp.Offset(, 1) = sum
        [COLOR=navy]Set[/COLOR] Temp = Dn
        sum = 0
    [COLOR=navy]Else[/COLOR]
        sum = sum + Dn.Value
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Click to expand...
 
Upvote 0
Hi.

Not sure I understand. The sum of those values (in L3:L9) is nowhere near 387. Neither is the sum of the values in L11:L17 anywhere near 418.

Regards
 
Upvote 0
Hi XOR,

Thank you for your reply...

It's a nice catch... Even I just noticed.... Just I prepared the example table with dummy data... I failed to notice that the Negative Integers were stored as text, so the summation was wrong...

But my requirement is the same... I need the summation of the cells in between two "Day-IN" cells...

[TABLE="width: 236"]
<TBODY>[TR]
[TD]1</SPAN>[/TD]
[TD]L </SPAN>[/TD]
[TD]M</SPAN>[/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD]Day-IN[/TD]
[TD="align: right"]271</SPAN>[/TD]
[/TR]
[TR]
[TD]3</SPAN>[/TD]
[TD]112</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4</SPAN>[/TD]
[TD]-65</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]90</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6</SPAN>[/TD]
[TD]-4</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7</SPAN>[/TD]
[TD]115</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8</SPAN>[/TD]
[TD]-47</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9</SPAN>[/TD]
[TD]70</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10</SPAN>[/TD]
[TD]Day-IN</SPAN>[/TD]
[TD="align: right"]319</SPAN>[/TD]
[/TR]
[TR]
[TD]11</SPAN>[/TD]
[TD]128</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12</SPAN>[/TD]
[TD]-56</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13</SPAN>[/TD]
[TD]144</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14</SPAN>[/TD]
[TD]-5</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15</SPAN>[/TD]
[TD]64</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16</SPAN>[/TD]
[TD]-38</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD]82</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18</SPAN>[/TD]
[TD]Day-IN</SPAN>[/TD]
[TD="align: right"]270</SPAN>[/TD]
[/TR]
[TR]
[TD]19</SPAN>[/TD]
[TD]86</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20</SPAN>[/TD]
[TD]-0</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22</SPAN>[/TD]
[TD]-51</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]23</SPAN>[/TD]
[TD]95</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]24</SPAN>[/TD]
[TD]-6</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]25</SPAN>[/TD]
[TD]130</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]26</SPAN>[/TD]
[TD]-56</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]27</SPAN>[/TD]
[TD]71</SPAN>[/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]


XOR LX said:
Hi.

Not sure I understand. The sum of those values (in L3:L9) is nowhere near 387. Neither is the sum of the values in L11:L17 anywhere near 418.

Regards
Click to expand...
 
Upvote 0
Thanks. But how do you arrive at a result in cell M18? There are no more entries of "Day-IN" in column L.

Regards
 
Upvote 0
Xor,

The Value of cell L28 is "Day-IN"...

It goes-on.... The collumn L has more than 1000 records... with "Day-In" in-between...

Hope u understand...



XOR LX said:
Thanks. But how do you arrive at a result in cell M18? There are no more entries of "Day-IN" in column L.

Regards
Click to expand...
 
Upvote 0
So I'll take it by that the last entry in the column is always "Day-IN".

In M2:

=IF(L2="Day-IN",SUM(L3:INDEX(L3:L$1048576,MATCH("Day-IN",L3:L$1048576,0)-1)),"")

Copy down as required.

Regards
 
Upvote 0
Thanks a Million Xor....

It's working like a charm..... :-)

Basically I'm trying to calculate the Total-Time Spent by an employee Inside and Outside a Datacenter, using the Swipe Report...

[TABLE="width: 227"]
<TBODY>[TR]
[TD]System Date Time</SPAN>[/TD]
[TD]Terminal</SPAN>[/TD]
[/TR]
[TR]
[TD]06/Apr/15 03:10 PM</SPAN>[/TD]
[TD]IN</SPAN>[/TD]
[/TR]
[TR]
[TD]06/Apr/15 05:03 PM</SPAN>[/TD]
[TD]OUT</SPAN>[/TD]
[/TR]
[TR]
[TD]06/Apr/15 06:09 PM</SPAN>[/TD]
[TD]IN</SPAN>[/TD]
[/TR]
[TR]
[TD]06/Apr/15 07:39 PM</SPAN>[/TD]
[TD]OUT</SPAN>[/TD]
[/TR]
[TR]
[TD]06/Apr/15 07:44 PM</SPAN>[/TD]
[TD]IN</SPAN>[/TD]
[/TR]
[TR]
[TD]06/Apr/15 09:39 PM</SPAN>[/TD]
[TD]OUT</SPAN>[/TD]
[/TR]
[TR]
[TD]06/Apr/15 10:26 PM</SPAN>[/TD]
[TD]IN</SPAN>[/TD]
[/TR]
[TR]
[TD]06/Apr/15 11:37 PM</SPAN>[/TD]
[TD]OUT</SPAN>[/TD]
[/TR]
[TR]
[TD]07/Apr/15 02:56 PM</SPAN>[/TD]
[TD]IN</SPAN>[/TD]
[/TR]
[TR]
[TD]07/Apr/15 05:05 PM</SPAN>[/TD]
[TD]OUT</SPAN>[/TD]
[/TR]
[TR]
[TD]07/Apr/15 06:02 PM</SPAN>[/TD]
[TD]IN</SPAN>[/TD]
[/TR]
[TR]
[TD]07/Apr/15 08:26 PM</SPAN>[/TD]
[TD]OUT</SPAN>[/TD]
[/TR]
[TR]
[TD]07/Apr/15 08:31 PM</SPAN>[/TD]
[TD]IN</SPAN>[/TD]
[/TR]
[TR]
[TD]07/Apr/15 09:36 PM</SPAN>[/TD]
[TD]OUT</SPAN>[/TD]
[/TR]
[TR]
[TD]07/Apr/15 10:14 PM</SPAN>[/TD]
[TD]IN</SPAN>[/TD]
[/TR]
[TR]
[TD]07/Apr/15 11:36 PM</SPAN>[/TD]
[TD]OUT</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]



The Challenge is, there are 3 shifts:

6am - 3pm
2pm - 11pm
10pm - 7am (the next day)...

It is very difficult to calculate for the rolling days... Do you have any idea....
 
Upvote 0
I don't know what you mean, I'm afraid, since you haven't explained or, even better, given any expected results for this new scenario.

Regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Count - specific consecutive order
Replies
6
Views
1K
Peter_SSs
Peter_SSs
A
  • Solved
Create Named Range for each column based on column heading text.
Replies
5
Views
305
Cubist
Cubist
U
  • Solved
Help with Index and multiple matches that include sum
Replies
2
Views
336
usnapoleon
U
U
  • Question Question
VLOOKUP that will SUM data based on Date Range
2
Replies
18
Views
1K
UnitedCloud01
U
P
  • Solved
Calculating shortest to longest distances between Zip Codes (using latitude and longitude)
2 3
Replies
25
Views
2K
pawcoyote
P

Forum statistics

Threads
1,223,705
Messages
6,173,985
Members
452,540
Latest member
haasro02

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top