...use the COUNTIF statement, looking at a Column of...

Answer by Eric
Submitted on 8/25/2003
Rating:	Rate this answer:
Robin: The trick to using complex COUNTIF statements lies in using the contatenation sign ("&") to build your expression. It sounds more intimidating than it really is. I'll explain. Let's start with the easy part. The simplest way to get items within the last week is to use the TODAY() function, which returns today's date. You can perform math on the value of TODAY(), so something like TODAY() - 7 will give you a date seven days before today. So basically what you want, in your question, is all items from a range where the value is >= TODAY() - 7 The problem is, even though it looks right, Excel won't let you say something like this: =COUNTIF(Range, ">= TODAY() - 7") because Excel doesn't want to look that hard at what's inside the quotes. Anything beyond a simple value check in the quotes is too confusing for Excel, so it just gives you "0" as a result. (In case you're wondering where the zero comes from, at this point, Excel is looking for cells in your range that actually have the exact text inside the quotes. Paste that quoted text (">= TODAY() -7") into a cell inside your range and you'll see that the COUNTIF number changes to one.) Luckily for us, Excel has a concatenation operator - the ampersand ("&"). Concatenation just means to put things together end-to-end, and the concatenation operator tells Excel to take two (or more) pieces of information and stick them together as if they were one piece. For example, you could mix text and a function like this: ="Today is " & TEXT(TODAY(),"dddd") & "!" which, in the spreadsheet, would look something like: Today is Monday! So for your formula, instead of putting the whole thing in quotes, try using the concatenation sign like this: =COUNTIF(Range, ">=" & TODAY() - 7) That way, you take the calculation out of the quotes so Excel can see it better and work it out, then put it together with the greater-than-or-equal sign and feed the whole thing to the COUNTIF function, and voila. (You might need to tweak the comparison signs and/or the value you subtract from TODAY() to get exactly the range you want. Note also that the spacing between terms in the expressions above is completely optional; I've only included it to make them easier to read.) Hope this helps. Good luck with your spreadsheet! Cheers, Eric excelhelp@ericgerhardt.com

FAQS.ORG makes no guarantees as to the accuracy of the posts. Each post is the personal opinion of the poster. These posts are not intended to substitute for medical, tax, legal, investment, accounting, or other professional advice. FAQS.ORG does not endorse any opinion or any product or service mentioned mentioned in these posts.

<< Back to general questions

[ Home | FAQ-Related Q&As | General Q&As | Answered Questions ]

Question by Robin
Submitted on 7/17/2003
Related FAQ: N/A
Rating:	Rate this question:
I want to use the COUNTIF statement, looking at a Column of dates. I want to count the entries that are dated from a week ago today until today only. I can't seem to figure out the formula. Any Suggestions? Thanks - robolsson@aol.com

Answer by BJGE
Submitted on 8/12/2003
Rating:	Rate this answer:
I'm trying to use this formula to see how many SMITHS have NEW =COUNT('Not Open'!B2:C108,SMITHS,'Not Open'!B2:B101,NEW) Is there a way to do this so that I come up with a count and it shows on a different worksheet?

Answer by WaleedSeada
Submitted on 6/8/2006
Rating: Not yet rated	Rate this answer:
=COUNTIF('14-Issues'!C3:C100,"OPEN") I want to make 14 the Row() of the current row not fixed, I tried this: =COUNTIF("'"&TEXT(ROW(),"00")&"-Issues'!"C3:"'"&TEXT(ROW(),"00")&"-Issues'!"C100,"OPEN") BUT it doesn't work, any help !! Waleed

Your name or nickname:
If you'd like to create a new account or access your existing account, put in your password here:
Your answer: