How can I use count function in excel where I have several criter.

[Princess V]

I have a worksheet with data. There are several filters on this worksheet.
I need to obtain a count based on several criteria. What I need is a count
such as this.

Column A Column B Column C
Apple Green Big
Apple Red Small
Pear Green Big
Orange Peach Big
Apple Green Big

I need to know how I can (on a separate worksheet) obtain a count of all the
Big Green Apples on this worksheet. The answer should be 2.

Any Help?
PS: I dont have SQL or Excel Add in etc. Just a vanilla version of Excel.

 

[Frank Kabel]

Hi
use sUMPRODUCT. See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Aladin Akyurek]

You need a formula for multiconditional counting...

=SUMPRODUCT(--($A$2:$A$10="Apple"),--($B$2:$B$10="Green"),--($C$2:$C$10="Big"))

 

[Princess V]

This was of great help. So lets say I want to obtain a count of the reverse.
That is everything except for the big green apples?

 

[Aladin Akyurek]

This was of great help. So lets say I want to obtain a count of the
reverse.
That is everything except for the big green apples?
[...]

=SUMPRODUCT(--($A$2:$A$10&$B$2:$B$10&$C$2:$C$10<>""),--(($A$2:$A$10<>"Apple")+($B$2:$B$10<>"Green")+($C$2:$C$10<>"Big")>0))

 

[Princess V]

Sorry,

Let me elaborate some more.

In a spreadsheet of my own (a lot bigger then the one I have shown below) I
have to count everything except for certain things. Is there a symbol that
means "does not equal to?" I know that greater than is > and less then is <
etc....what is does not equal?

So for example, I want to count everything except apples and blanks in the
columns:

Column A Column B Column C
Apple Green Big
Apple Red Small
Pear Green Big
Orange Peach Big
Apple Green Big
Peach Red Small
Blank Blank Blank
Blank Blank blank (these kind of blank lines will be part of
the range specified)

Thanks.

 

[Frank Kabel]

Hi
<>
))

 

[Princess V]

Actually,

One more thing to add....sorry about this....

I want to know the count of everything except for apples in Column A that
are green and big. Without counting the blanks in column A.

 

[Aladin Akyurek]

Sorry,

Let me elaborate some more.

In a spreadsheet of my own (a lot bigger then the one I have shown
below) I
have to count everything except for certain things. Is there a symbol
that
means "does not equal to?" I know that greater than is > and less then
is <
etc....what is does not equal?

So for example, I want to count everything except apples and blanks...
[...]

<> means not equal.

Did you try the one I posted?

Perhaps this is better:

=SUMPRODUCT(--($A$2:$A$10&$B$2:$B$10&$C$2:$C$10<>""),--($A$2:$A$10&","&$B$2:$B$10&","&$C$2:$C$10<>"Apple,Green,Big"))

should to count all non-blank records that do not consist of Apple,
Green, and Big at the same time.

 

[Aladin Akyurek]

Actually,

One more thing to add....sorry about this....

I want to know the count of everything except for apples in Column
that
are green and big. Without counting the blanks in column A.
[...]

Gee. Did you try those last 2 formulas

 

[Princess V]

Aladin,

I feel so bad bugging you yet again. I think I may not be communicating my
issue properly - so I am going to present you with some different data and
perhaps you can help.

What you initially provided me (for multiconditioning) worked like a charm.
Where I am having a problem with is to come up with counts for the remaining
types of data in a particular column. So I will provide an additional
example closer to help me better communicate.

Column A Column B Column C
Linda Female Old
Roger Male Teen
Max Male Young
Linda Female Teen
Max Female Teen
Linda Female Old
Blank Blank Blank

So initially, I wanted to know How many Old Females named Linda existed.
The formula you initially suggested would work for this.

Now in a separate table, I want to obtain a count for how many Old Females
with any name except Linda exist - without counting any blanks.

So first want to determine the count of All names which are not Linda, and
from that remove all males from the count...and then remove anyone who is not
Old from the count.

Does your suggested formula still apply?

--> So sorry to be bothering you about this......

=)

Sorry,

Let me elaborate some more.

In a spreadsheet of my own (a lot bigger then the one I have shown
below) I
have to count everything except for certain things. Is there a symbol
that
means "does not equal to?" I know that greater than is > and less then
is <
etc....what is does not equal?

So for example, I want to count everything except apples and blanks...
[...]

<> means not equal.

Did you try the one I posted?

Perhaps this is better:

=SUMPRODUCT(--($A$2:$A$10&$B$2:$B$10&$C$2:$C$10<>""),--($A$2:$A$10&","&$B$2:$B$10&","&$C$2:$C$10<>"Apple,Green,Big"))

should to count all non-blank records that do not consist of Apple,
Green, and Big at the same time.

 

[Aladin Akyurek]

Aladin,

[...]

Now in a separate table, I want to obtain a count for how many Ol
Females
with any name except Linda exist - without counting any blanks.

[...]

Does your suggested formula still apply?

[...]

No. That's a different question than I gathered from your earlie
requests.

I guess this would...

=SUMPRODUCT(($B$2:$B$10&","&$C$2:$C$10="Female,Old")-($A$2:$A$10&","&$B$2:$B$10&","&$C$2:$C$10="Linda,Female,Old"))

Note that there is just one minus

 

[Princess V]

Thanks again......I used this formula - and yielded the same thing as I was
getting using a different formula previously. But the value being returned
by the formula is not consistent with what I get when I manually count the
lines that satisfy the criteria specified. I now feel that there must be an
issue with the way the data is set up in the worksheet.

Let me ask you - one of my columns is a list of departments that are a
combination of numeric and letters. Example, 702R and 702HG. So when I
specify within the formula - I am using:

=SUMPRODUCT((Matrix!$K$6:$K$1000&","&Matrix!$P$6:$P$1000="spoc,started")-(Matrix!$C$6:$C$1000&","&Matrix!$K$6:$K$1000&","&Matrix!$P$6:$P$1000="702r,spoc,started"))

(Where matrix is the name of the worksheet that I have my data in)

For some reason, all of those lines with 702HG is listed, these records are
not counted.

Is this because it is made up of letters and numbers?

I am so confused....

Aladin,

[...]

Now in a separate table, I want to obtain a count for how many Old
Females
with any name except Linda exist - without counting any blanks.

[...]

Does your suggested formula still apply?

[...]

No. That's a different question than I gathered from your earlier
requests.

I guess this would...

=SUMPRODUCT(($B$2:$B$10&","&$C$2:$C$10="Female,Old")-($A$2:$A$10&","&$B$2:$B$10&","&$C$2:$C$10="Linda,Female,Old"))

Note that there is just one minus.

 

[Princess V]

Aladin,

I believe that I may have found the root cause of my issue. Seems like some
rows were not being counted because it looks like data was entered using a
Copy&Paste function.

Do you think that this is the case with formula's within excel spreadsheet?
Is my thinking correct?

Aladin,

[...]

Now in a separate table, I want to obtain a count for how many Old
Females
with any name except Linda exist - without counting any blanks.

[...]

Does your suggested formula still apply?

[...]

No. That's a different question than I gathered from your earlier
requests.

I guess this would...

=SUMPRODUCT(($B$2:$B$10&","&$C$2:$C$10="Female,Old")-($A$2:$A$10&","&$B$2:$B$10&","&$C$2:$C$10="Linda,Female,Old"))

Note that there is just one minus.

 

[hgrove]

Princess V wrote...

. . . I used this formula - and yielded the same thing as I wa getting using a
different formula previously. But the value being returned by th formula is not
consistent with what I get when I manually count the lines tha satisfy the
criteria specified. I now feel that there must be an issue with th way the data
is set up in the worksheet.

Let me ask you - one of my columns is a list of departments that ar a
combination of numeric and letters. Example, 702R and 702HG. So whe I
specify within the formula - I am using:

=SUMPRODUCT((Matrix!$K$6:$K$1000&","&
Matrix!$P$6:$P$1000="spoc,started")
-(Matrix!$C$6:$C$1000&","&Matrix!$K$6:$K$1000&","&
Matrix!$P$6:$P$1000="702r,spoc,started"))

...

If the formula results in a smaller count than expected, then th
reason is almost always trailing spaces after entries in some of th
data ranges. You could check this by checking parts of the formulas
Do

=COUNTIF(Matrix!$K$6:$K$1000,"spoc")

and

=COUNTIF(Matrix!$K$6:$K$1000,"spoc*")

give the same result? Ditto for col P and "started" and col C an
"702R". If any of these pairs of counts differ, you almost certainl
have trailing spaces you'l need to remove