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