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

  • So really the trigger is when the cell below = Mon but current cell not = Mon? – Eric F Jan 25 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 Jan 25 at 22:38
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

  • 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 Jan 25 at 16:10
  • See my edited formula – cybernetic.nomad Jan 25 at 16:16
  • A little bit complicated but it does the work. Thank you! – Endri Jan 25 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.