Forum Discussion

Sdx013's avatar
Sdx013
Copper Contributor
Jan 07, 2022
Solved

SUMIFS Using todays Date

I think SUMIF is the way to do this. I'd like a cell (E5 in example) to give me the sum of cells (F5:L5) for the current week (F4:L4) by using todays date (A2) and then be able do it for the date ranges beyond that. 

excel
Formulas and Functions
  • Sdx013's avatar
    Sdx013
    Jan 07, 2022
    Thank you, I will plug it in and check it out. I'm trying to figure it out in 365 but ultimately I'd like to move it to Sheets so that I can share it easily

6 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jan 07, 2022

    Sdx013 

    There are two distinct approaches to the problem.  One is, as you suggest, to use SUMIFS to aggregate counts within the selected week.  The other is to match the Sunday and return the week's counts.  Using 365 for clarity

    = LET(
    Sundays,       IF(WEEKDAY(date)=1,date),
    selectedWeek,  XMATCH(selectedDate, Sundays,-1) + {0,1,2,3,4,5,6},
    selectedCount, INDEX(count, selectedWeek),
    SUM(selectedCount)
  )

    or, using legacy versions of Excel,

    = SUM(
    INDEX( 
      count,
      MATCH(
        selectedDate,
        IF(WEEKDAY(date)=1,date)
      ) + {0,1,2,3,4,5,6}
    )
  )
    • Sdx013's avatar
      Sdx013
      Copper Contributor
      Jan 07, 2022
      Thanks Peter, I will have to sit down and look at it closer to see which I can comprehend better

Resources