Excel COUNTIF increment date for pasting

0
votes

I have a transaction table with two columns- ID and Date. Each id is unique and never repeated.

I want to count the number of transactions for each day of the past few years, without typing the formula for each day. When I try to pull down the formula =COUNTIF(B2:B5000, "1/1/17") to cells below, it changes the cell range rather than the date.

1
excelcountif
Is a pivot table an option? – BigBen
Trying this, will report back. – RickP
If you are going to be adding data to the Range being used, then I would strongly suggest putting it into a Table (select your data including column headings and press ctrl+t). Doing so let's you use the syntax =COUNTIF(Table1[[ID]:[Date]],[@[Date]]). Now when you add data to your table, the formula will always be correct and you get the additional benefit of your formula being self-commenting. – Frank Ball

1 Answers

1
votes

Try

 =COUNTIF($B$2:$B$5000,DATE(2017,1,ROW(A1)))