COUNTIFS function...

**ericarodgers** · 03-09-2013, 08:43 AM

Hi everyone,

This is my first post so please let me know if I'm doing anything wrong

Please see my file attached.

I need your help to setup a formula the counts cells based on the date and the content of the cell above it.

I want to setup formulas in B90 to B97 that count the number of times the letter in A90 to A97 appears in E8:R78 if the date in the cell underneath is over a week from TODAY (<TODAY()-7).

Sorry if I haven't explained this well. Any guidance you could give would be much appreciated!

COUNTIFS help please.xlsx

**Harribone** · 03-09-2013, 09:55 AM

Hi ericarodgers, welcome to the forum!

Becuase of the layout for your sheet the only thing I can suggest is to implement a formula for the R's (and other letters), so for example in E10:

=IF(E11<TODAY()-7,"R ","R")

Note the space after R if the date is over a week.

Then in the count cells you can count how many times "R " appears. So in B90 the count formula would then be:

=COUNTIF($E$8:$S$78,A90&" ")

Note that this would mess up you total count and this needs changing too so that it totals correctly. For example B83 would now need to be:

=COUNTIF($E$8:$S$78,A84)+COUNTIF($E$8:$S$78,A84&" ")

Also conditional formatting rules need to be added to include "R " as well as "R". The same applies to all letters used throughout the sheet. Not ideal I know because this will take time to modify the whole of your worksheet but hopefully this will save time overall when you start to use it.

**daddylonglegs** · 03-09-2013, 11:12 AM

Try this formula for B89

=COUNTIFS($E$8:$S$78,A89,$E$9:$S$79,"<"&TODAY()-7)

Notice how the 2nd range is "offset" by 1 row from the first

Drag down to B92

**ericarodgers** · 03-10-2013, 01:34 AM

Thank you daddylonglegs and harribone!

I used the =COUNTIFS($E$8:$S$78,A89,$E$9:$S$79,"<"&TODAY()-7)and it worked a treat.

Thanks again for your assistance