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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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