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

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.

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

--
Regards
Frank Kabel
Frankfurt, Germany


Princess V wrote:
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.

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 Wrote:
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.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274322

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" wrote:


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 Wrote:
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.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274322

Princess V Wrote:
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))


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274322

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.






"Princess V" wrote:

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" wrote:


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 Wrote:
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.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274322

Hi
<
:-)))

--
Regards
Frank Kabel
Frankfurt, Germany

"Princess V" schrieb im
Newsbeitrag ...
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.






"Princess V" wrote:

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" wrote:


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 Wrote:
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.


--
Aladin Akyurek

---------------------------------------------------------------------
---
Aladin Akyurek's Profile:
http://www.excelforum.com/member.php...fo&userid=4165
View this thread:
http://www.excelforum.com/showthread...hreadid=274322

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.

"Princess V" wrote:

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.






"Princess V" wrote:

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" wrote:


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 Wrote:
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.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274322

Princess V Wrote:
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 Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274322

Princess V Wrote:
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.
[...]

Gee. Did you try those last 2 formulas?


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274322

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......

=)








"Aladin Akyurek" wrote:


Princess V Wrote:
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 Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274322

Princess V Wrote:
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,F emale,Old"))

Note that there is just one minus.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274322

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&","&M atrix!$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 Akyurek" wrote:


Princess V Wrote:
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,F emale,Old"))

Note that there is just one minus.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274322

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 Akyurek" wrote:


Princess V Wrote:
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,F emale,Old"))

Note that there is just one minus.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274322

Princess V wrote...
. . . 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"))
...

If the formula results in a smaller count than expected, then the
reason is almost always trailing spaces after entries in some of the
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 and
"702R". If any of these pairs of counts differ, you almost certainly
have trailing spaces you'l need to remove.


--
hgrove
------------------------------------------------------------------------
hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432
View this thread: http://www.excelforum.com/showthread...hreadid=274322