Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Princess V
 
Posts: n/a
Default 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.



  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

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.

  #3   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


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

  #4   Report Post  
Princess V
 
Posts: n/a
Default

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


  #5   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


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



  #6   Report Post  
Princess V
 
Posts: n/a
Default

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


  #7   Report Post  
Frank Kabel
 
Posts: n/a
Default

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



  #8   Report Post  
Princess V
 
Posts: n/a
Default

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


  #9   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


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

  #10   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


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



  #11   Report Post  
Princess V
 
Posts: n/a
Default

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


  #12   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


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

  #13   Report Post  
Princess V
 
Posts: n/a
Default

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


  #14   Report Post  
Princess V
 
Posts: n/a
Default

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


  #15   Report Post  
hgrove
 
Posts: n/a
Default


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
Access Module coded converted to Excel Function Adam Excel Discussion (Misc queries) 1 December 23rd 04 03:48 PM
Excel function help facilities RPS Excel Discussion (Misc queries) 1 December 8th 04 03:36 AM
I cant use englisch function names in a swedich version of excel PE Excel Discussion (Misc queries) 2 December 7th 04 02:00 AM
Statistical Excel Function Question within Excel 2000... Drew H Excel Worksheet Functions 3 October 31st 04 07:55 PM


All times are GMT +1. The time now is 08:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"