Forum Discussion
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?
=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.
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)
