# Thread: Adding number of times code appears in cells over a rolling period Thanks:  1 Post #5293296 (1) Likes:  1 Post #5293296 (1)

1. ## Adding number of times code appears in cells over a rolling period

I have a formula, found elsewhere, that counts the totals of cells from current date – backwards for one year
=SUMIFS(\$L\$5:\$BDQ\$5,\$L\$3:\$BDQ\$3,"<="&F37,\$L\$3:\$BDQ\$3,">="&DATE(YEAR(F37)-1,MONTH(F37),DAY(F37)))

Data in \$L\$5:\$BDQ\$5
Dates in ,\$L\$3:\$BDQ\$3
Todays date in \$F\$37

This works perfect for numerical values in the cells. However, I have a spread sheet that has “codes” as values in the cells: Two of the codes are “SO” & “S”. I need to count (sum) these as a rolling period from current date to one year previous
Code used for COUNTIF.
=COUNTIF(L5:BDQ5,"=SO")+COUNTIF(L5:BDQ5,"=S")

Unfortunately I cannot work out how integrate the COUNTIF instead of the SUMIFS

Probably just missing something simple? Any helps would be much appreciated

2. ## Re: Adding number of times code appears in cells over a rolling period

I think it would be helpful to see an example of your data, along with your expected results.

3. ## Re: Adding number of times code appears in cells over a rolling period

Maybe...

=SUM(COUNTIFS(\$L\$5:\$BDQ\$5,{"S","SO"},\$L\$3:\$BDQ\$3,"<="&F37,\$L\$3:\$BDQ\$3,">="&DATE(YEAR(F37)-1,MONTH(F37),DAY(F37))))

M.

4. ## Re: Adding number of times code appears in cells over a rolling period

Originally Posted by Marcelo Branco
Maybe...

=SUM(COUNTIFS(\$L\$5:\$BDQ\$5,{"S","SO"},\$L\$3:\$BDQ\$3,"<="&F37,\$L\$3:\$BDQ\$3,">="&DATE(YEAR(F37)-1,MONTH(F37),DAY(F37))))

M.
Thank you very much Marcelo. Much appreciated.

5. ## Re: Adding number of times code appears in cells over a rolling period

You are welcome. Thanks for the feedback.

M.