Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have the following formula:

{=SUM(('Data 2'!D:D>=VALUE("17-September-2012"))('Data 2'!K:K="0 out of 1"))-SUM(('Data 2'!D:D>="None Specified")('Data 2'!K:K="0 out of 1"))}

This works fine to count the data which is dated after the 17th september with O out of 1 in column K, however I want to count the number of cells where the contents contain O out of*

However it seems that wildcards are not allowed in loical statements in SUM?

Is there a better way for me to do this?


share|improve this question

migrated from Jan 10 '13 at 13:59

This question came from our site for professional and enthusiast programmers.

up vote 1 down vote accepted

I assume you have Excel 2007 or later (otherwise your formula won't work) so try using COUNTIFS like this

=COUNTIFS('Data 2'!D:D,">="&DATE(2012,9,17),'Data 2'!K:K,"0 out of*")

That will recognise the wildcard and ignore any text entries like "None Specified"

share|improve this answer
Thanks a lot - I actually solved this with a massive workaround, your solution is a lot neater! Here is mine (with some extra stuff added in) - =SUM(('Data 2'!D:D>=VALUE("17-September-2012"))*(ISNUMBER(SEARCH("0 out of",'Data 2'!K:K))))-SUM(('Data 2'!D:D>="None Specified")*(ISNUMBER(SEARCH("0 out of",'Data 2'!K:K))))+SUM(('Data 2'!D:D>=VALUE("17-September-2012"))*('Data 2'!K:K="-"))-SUM(('Data 2'!D:D>="None Specified")*('Data 2'!K:K="-")) – user25704 Jan 10 '13 at 11:43

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.