Countif ?

[MT]

I want a summary count of two criteria. (count if type = a and class =1)

In one column type is stored and in another column Classification is stored.

I want the know how many type a, classification 1 exist

I was trying to use countif, but can't seem to figure out how to do it for
two criteria. can you use AND??

=COUNTIF(Sheet1!E:E,"IC")

[Paul Lautman]

MT wrote:
> I want a summary count of two criteria. (count if type = a and class
> =1)
>
> In one column type is stored and in another column Classification is
> stored.
>
> I want the know how many type a, classification 1 exist
>
> I was trying to use countif, but can't seem to figure out how to do
> it for two criteria. can you use AND??
>
> =COUNTIF(Sheet1!E:E,"IC")

You say that your criteria are a and 1 yet your COUNTIF contains IC???

Are your types and classes in separate columns? If so where?

What actually are your criteria values?

[jcc]

Fluke, but I just happened to look into it this morning!

What you actually want is multiple-condition formula. If you seach Excel
Help under "Sum", there is a great explanation of how to Sum using multiple
conditions. I do not know why the multiple-condition formula option does not
show when you seach "Count", but it does not.

Basically, the key difference is that you have to use an array formula
(another thing I learned this morning). The most important thing to remember
with an array formula is to use brace brackets {} around the entire formula.
You do this by either manually entering them, or by holding CTRL-SHIFT-ENTER
after you finish typing the formula.

The formula you use should look like this:

{=COUNT(IF(($B$4:$B$8=$A24)*($E$4:$E$8=B$20),$C$4:$C$8))}

Where A24 is the first condition you want met; B4:B8 is the column in which
you want this condition to be found; B20 is the second condition you want
met; E4: E8 is the column you want this condition to be found; C4:C8 is the
column you want counted.

Some items of note: I used reference cells, but if you want the condition to
be a word, you can just type in "Apples" instead of A24 or B20. Also, I have
found the exact column to be counted (C4:C8) to be somewhat irrelevant so
long as it is any column in your array of data.

Good luck!

"MT" wrote:

> I want a summary count of two criteria. (count if type = a and class =1)
>
> In one column type is stored and in another column Classification is stored.
>
> I want the know how many type a, classification 1 exist
>
> I was trying to use countif, but can't seem to figure out how to do it for
> two criteria. can you use AND??
>
> =COUNTIF(Sheet1!E:E,"IC")

[Toppers]

Try (changes ranges to suit):

=SUMPRODUCT(--(A1:A5="a"),--(B1:B5=1))

"jcc" wrote:

> Fluke, but I just happened to look into it this morning!
>
> What you actually want is multiple-condition formula. If you seach Excel
> Help under "Sum", there is a great explanation of how to Sum using multiple
> conditions. I do not know why the multiple-condition formula option does not
> show when you seach "Count", but it does not.
>
> Basically, the key difference is that you have to use an array formula
> (another thing I learned this morning). The most important thing to remember
> with an array formula is to use brace brackets {} around the entire formula.
> You do this by either manually entering them, or by holding CTRL-SHIFT-ENTER
> after you finish typing the formula.
>
> The formula you use should look like this:
>
> {=COUNT(IF(($B$4:$B$8=$A24)*($E$4:$E$8=B$20),$C$4:$C$8))}
>
> Where A24 is the first condition you want met; B4:B8 is the column in which
> you want this condition to be found; B20 is the second condition you want
> met; E4: E8 is the column you want this condition to be found; C4:C8 is the
> column you want counted.
>
> Some items of note: I used reference cells, but if you want the condition to
> be a word, you can just type in "Apples" instead of A24 or B20. Also, I have
> found the exact column to be counted (C4:C8) to be somewhat irrelevant so
> long as it is any column in your array of data.
>
> Good luck!
>
> "MT" wrote:
>
> > I want a summary count of two criteria. (count if type = a and class =1)
> >
> > In one column type is stored and in another column Classification is stored.
> >
> > I want the know how many type a, classification 1 exist
> >
> > I was trying to use countif, but can't seem to figure out how to do it for
> > two criteria. can you use AND??
> >
> > =COUNTIF(Sheet1!E:E,"IC")