0

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

1

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"

1
  • 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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.