-1

I have X,Y data in two columns (A,B) of an Excel spreadsheet. I want to count the values in the B column using a criteria of the A-column. Specifically, the A,B column data are dates and occurrences of an event on that given day. But I want the counts based on each week, not each day (column E in the image). COUNTIF won't work but was wondering how this can be accomplished. As you can see in the spreadsheet, there are two occurrence for the week of 9/30/2018 to 10/6/2018 (1 event each on 10/3 and 9/30), so in cell F2 we have 2.

Need Formula to count by week

Mike Stephenson
  • 649
  • 6
  • 14

1 Answers1

2

This can be done with SUMIFS, e.g. type this into cell F2 and drag down:

= SUMIFS(B:B,A:A,"<="&E2,A:A,">="&(E2-6))

See below working example.

enter image description here

ImaginaryHuman072889
  • 4,953
  • 7
  • 19
  • 51