Calculating by. Date and columns

Copper Contributor

I have a worksheet which has a column “Reason For Visit” which has various descriptors within each cell.  I.e. “food”, “lawyer”,”socializing”.  There is another column with dates.  I want to calculate how many individual items in the column (food) for a specified date range.  How do I do that?

2 Replies



Putting dates in column A, reasons in column B, I've come up with two formulas that work. I extended columns A and B down to row 41, just to give the formulas something to work with.

The first formula requires a newer version of Excel than you have, in order for the FILTER function to work.


The syntax of the second is tricky, but it does work. I had to refer to this site to get it right (those quote marks around the <= and >= in setting the date parameters)





=SUMPRODUCT(LEN(TRIM(INDIRECT("A"&IF(NOT(ISNA(MATCH($D$1,$B$2:$B$8,0))),MATCH($D$1,$B$2:$B$8,0)+1,MATCH($D$1,$B$2:$B$8,1)+2)):INDIRECT("A"&MATCH($D$2,$B$2:$B$8,1)+1)))-LEN(SUBSTITUTE(INDIRECT("A"&IF(NOT(ISNA(MATCH($D$1,$B$2:$B$8,0))),MATCH($D$1,$B$2:$B$8,0)+1,MATCH($D$1,$B$2:$B$8,1)+2)):INDIRECT("A"&MATCH($D$2,$B$2:$B$8,1)+1)," ",""))+1)


You can try this formula if you want to count several words within every cell. The date column has to be in ascending order.

calculating by date and column.JPG