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

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

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