Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

Thanks

share|improve this question

migrated from stackoverflow.com Jan 10 '13 at 13:59

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

add comment

1 Answer

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
add comment

Your Answer

 
discard

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