Shortening an Excel formula if possible

Question

I am unable to shorten an Excel formula I am using in an Excel database. I've googled it, but this appears to be already the shortest way from the first page on Google.

=COUNTIF(K:K,"Busy")+COUNTIF(K:K,"Hangup")+COUNTIF(K:K,"Stopped")+COUNTIF(K:K,"Voicemail")+COUNTIF(K:K,"Wrong Number")+COUNTIF(K:K,"No Answer")+COUNTIF(K:K,"IP Phone Offline")+COUNTIF(K:K,"Call accepted")

The formula isn't pretty and, on the report I need to produce about this database, it stands out as looking quite simply horrible.

Can anyone give me a pointer in the right syntax OR send through the correct way to do this but annotated so I can learn how to do it in the future?

Thanks!

Edd

Answer 1

=SUM(COUNTIF(K:K,{"Busy","Hang up","Stopped","Voicemail","Wrong Number","No Answer","IP Phone Offline","Call accepted"}))

If this list is subject to change/expansion then it would make more sense to store the criteria in cells within the worksheet, rather than within the formula itself. For example, if the criteria were in A1:A8, the required formula would be:

=SUMPRODUCT(COUNTIF(K:K,A1:A8))

Regards

Answer 2

You can use the following to make it look good.

1) Go to Formulas tab - under Defined names group - select Name manager (Or for old versions of excel using menus - Insert>Name>Define) and in the "Names" box type: calldetails.

2) Scope - can be workbook.

3) Now click in the "Refers to" box and type (or paste): =COUNTIF(K:K,"Busy")+COUNTIF(K:K,"Hang up")+COUNTIF(K:K,"Stopped")+COUNTIF(K:K,"Voicemail")+COUNTIF(K:K,"Wrong Number")+COUNTIF(K:K,"No Answer")+COUNTIF(K:K,"IP Phone Offline")+COUNTIF(K:K,"Call accepted") then click Add.

Now anywhere you want to use this formula just type =calldetails

This makes it look neat and tidy