Counting Multiple Options

**johnlovesbeer** · 12-06-2009, 10:12 AM

Hi all,

Having great success at using Countif and Sumif in totaling values across two sheets, but at the risk of sounding like a dummy I would like a suggestion as to which formula I should be using for this basic little function;

I have a column on a sheet with dropdowns offering 4 options; Active, Cancelled, Completed, and Suspended. I'm using Countif to total the number of "Active" but I also need to total the number of "Canceled", "Completed", and "Suspended" under the one heading, as if they all meant the same thing.
(I hope that makes sense).

Cheers,

JLB.

**martindwilson** · 12-06-2009, 10:18 AM

well the easiest way would be to sum the countifs! or since they all end in d and theres no "d" in active
=COUNTIF(A1:A100,"*d")

**DonkeyOte** · 12-06-2009, 10:18 AM

EDIT: mdw's is best... sneaky mdw!

If you have only the 4 options and you may have blanks then:

=COUNTA(Sheet2!A:A)-COUNTIF(Sheet2!A:A,"Active")

else

=SUM(COUNTIF(Sheet2!A:A,{"Cancelled","Completed","Suspended"}))

**johnlovesbeer** · 12-06-2009, 10:44 AM

Oops, don't think I explained correctly. (Also made a boo-boo)

I am only using the 1 sheet for this one initially and I'm actually already totaling the "Completed" with countif, not "Active" as I first posted (sorry). The 4 options only appear in 1 column (AB 14 to AB 5033) and yes, there will be blanks which are not to be counted. I need to total Active, Cancelled and Suspended as 1 entity so I can show "Completed" against "Not Completed" if you get my drift, so Active, Cancelled or Suspended will count as a "Not Completed" unit. The totals will of course only show as 0 or 1 in column AR but I will then use Sumif to send complete totals to a 2nd sheet.

I still probably sound confusing but I think that describes it okay.

JLB.

**DonkeyOte** · 12-06-2009, 11:04 AM

Using the COUNTA example change "Active" to "Completed"

=COUNTA(AB14:AB5033)-COUNTIF(AB14:AB5033,"Completed")

Using the SUM(COUNTIF example change "Completed" to "Active"

=SUM(COUNTIF(AB14:AB5033,{"Active","Cancelled","Suspended"}))

**johnlovesbeer** · 12-06-2009, 11:28 AM

Sorry Guys, I must be thick.

Doesn't seem to matter how I type in the command it gives me an error. Here are the cell details;
Cells AB14 to AB5033 have dropdown options 'Active, Cancelled, Completed, Suspended'.
Column AP is using a countif to show if 'Completed' has been chosen in column AB and is working well. The count in any cell in AP is of course either 0 or 1. That's OK.
Column AQ is where I want to show if either of the other 3 options in AB have been chosen. Again it will of course only total to 0 or 1 in each cell in AQ. That's OK. I just can't get it to happen.
Having a total that only shows 0 or 1 may sound odd but it suits my purpose. All other formulas and functions that I have on the sheet are working well but this one is giving me a real headache. Surely it is simpler than the hassle it is giving me!

Cheers,

JLB.