0

I have an Excel sheet with two columns:

  • A - contain the days of the week (Mon, Twe, Wed, Thu, Fri - maybe rarely even Sat and Sun)
  • B - contain a number

The records may contain not a fixed amount of records for each day, so for example Mon can appear in 3 consecutive rows, while Tue only once. So for a week we have more records than the days in one week. Also the days of the week in column A are sorted.

What I want is the sum of those number based when week changes. By change of the week it mean when we have on first record Fri and in the next Mon or Thu and Mon.

How can I write a formula in C column that it shows the result in the last day of each week?

Sample column data

Improve this question
2
  • So really the trigger is when the cell below = Mon but current cell not = Mon?
    – Eric F
    Commented Jan 25, 2019 at 16:00
  • 1
    Is the first day of data always a Monday? For example, could you ever run into a situation where one week had data for just Monday and Tuesday, and the following week's first entry was for Wed?
    – fixer1234
    Commented Jan 25, 2019 at 22:38

1 Answer 1

Reset to default
5

Use the following formula in C2:

=IFERROR(IF(ISBLANK(A3),SUM(B$2:B2)-SUM(C$1:C1),IF(MATCH(A2,{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},)>MATCH(A3,{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},),SUM(B$2:B2)-SUM(C$1:C1),"")),"")

And populate down

Improve this answer
3
  • This is almost what I want, but it doesn't produce the result for the last week. Should I add a dummy record at the end or can it be modified to get the sum for the last week?
    – Endri
    Commented Jan 25, 2019 at 16:10
  • See my edited formula
    – cybernetic.nomad
    Commented Jan 25, 2019 at 16:16
  • A little bit complicated but it does the work. Thank you!
    – Endri
    Commented Jan 25, 2019 at 16:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .