Calculating by. Date and columns

[terri2751]

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]

@terri2751 

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)

[#3]

@terri2751 

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