New Contributor

# Calculating by. Date and columns

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

# Re: Calculating by. Date and columns

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.

``=COUNTA(FILTER(B2:B41,(B2:B41=E2)*(A2:A41>=F2)*(A2:A41<=G2)))``

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)

``=COUNTIFS(B2:B41,E2,A2:A41,">="&F2,A2:A41,"<="&G2)``

# Re: Calculating by. Date and columns

=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.