counting using multiple criteria

Hi,

I have a problem for which I can't seem to find a working solution.  On the 
one hand I have a vertical table with employee names, the number of years 
they have been with the company, and all this sorted by the employee's age 
(in years).  This table will be updated from time to time, and as such it 
doesn't have a fixed length.

On the other hand I have a table which shows the number of employees in a 
particular age category (-20, 21-25, 26-30, etc) horizontally, and the number 
of years they have been with the company (again in groups: 0-5 yrs, 5-10 yrs, 
etc) vertically.

From this table, which is currently being updated manually, one can see that 
for example, the company has 8 employees in the category 31 to 35 years, who 
have been with the company 0 to 5 years, etc.

Is there a function to count the number of people who match both criteria to 
fit into a specific cell of the table?
0
8/25/2005 11:58:12 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
687 Views

Similar Articles

[PageSpeed] 45

sumproduct() works well for this type of application
=sumproduct(--(and(Agerange>=Age1,Agerange<=Age2),--(and(Servicerange>=time1,Servicerange<=time2))
ther arrays in each section need to be the same size and cannot reference 
the shorthand for entire rows or columns.

"SyntaX TerroR" wrote:

> Hi,
> 
> I have a problem for which I can't seem to find a working solution.  On the 
> one hand I have a vertical table with employee names, the number of years 
> they have been with the company, and all this sorted by the employee's age 
> (in years).  This table will be updated from time to time, and as such it 
> doesn't have a fixed length.
> 
> On the other hand I have a table which shows the number of employees in a 
> particular age category (-20, 21-25, 26-30, etc) horizontally, and the number 
> of years they have been with the company (again in groups: 0-5 yrs, 5-10 yrs, 
> etc) vertically.
> 
> From this table, which is currently being updated manually, one can see that 
> for example, the company has 8 employees in the category 31 to 35 years, who 
> have been with the company 0 to 5 years, etc.
> 
> Is there a function to count the number of people who match both criteria to 
> fit into a specific cell of the table?
0
BJ (832)
8/25/2005 12:28:11 PM
The standard approach for counting with multiple criteria is
   =SUMPRODUCT((criteria1)*(criteria2)*...)
The explicit multiplication coerces the boolean arrays to 0's (FALSE) 
and 1's (TRUE), so that the subsequent sum is equivalent to counting.

Jerry

SyntaX TerroR wrote:

> Hi,
> 
> I have a problem for which I can't seem to find a working solution.  On the 
> one hand I have a vertical table with employee names, the number of years 
> they have been with the company, and all this sorted by the employee's age 
> (in years).  This table will be updated from time to time, and as such it 
> doesn't have a fixed length.
> 
> On the other hand I have a table which shows the number of employees in a 
> particular age category (-20, 21-25, 26-30, etc) horizontally, and the number 
> of years they have been with the company (again in groups: 0-5 yrs, 5-10 yrs, 
> etc) vertically.
> 
> From this table, which is currently being updated manually, one can see that 
> for example, the company has 8 employees in the category 31 to 35 years, who 
> have been with the company 0 to 5 years, etc.
> 
> Is there a function to count the number of people who match both criteria to 
> fit into a specific cell of the table?

0
post_a_reply (1395)
8/25/2005 12:33:48 PM
Assuming that your results table is in E1:J7 (F1=0-20, G1=21-25, etc.
E2=0-5,E3-5-10, etc), then use

=SUMPRODUCT((length_of_service>--LEFT($E3,FIND("-",$E3)-1))*(length_of_servi
ce<=--RIGHT($E3,LEN($E3)-FIND("-",$E3)))*(age>=--LEFT(G$1,FIND("-",G$1)-1))*
(age<=--RIGHT(G$1,LEN(G$1)-FIND("-",G$1))))

where length_of_service  is the years in the job column, age is the age in
years column.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"SyntaX TerroR" <SyntaXTerroR@discussions.microsoft.com> wrote in message
news:0C1291B3-816A-4014-9E0A-01E741E6C186@microsoft.com...
> Hi,
>
> I have a problem for which I can't seem to find a working solution.  On
the
> one hand I have a vertical table with employee names, the number of years
> they have been with the company, and all this sorted by the employee's age
> (in years).  This table will be updated from time to time, and as such it
> doesn't have a fixed length.
>
> On the other hand I have a table which shows the number of employees in a
> particular age category (-20, 21-25, 26-30, etc) horizontally, and the
number
> of years they have been with the company (again in groups: 0-5 yrs, 5-10
yrs,
> etc) vertically.
>
> From this table, which is currently being updated manually, one can see
that
> for example, the company has 8 employees in the category 31 to 35 years,
who
> have been with the company 0 to 5 years, etc.
>
> Is there a function to count the number of people who match both criteria
to
> fit into a specific cell of the table?


0
bob.phillips1 (6510)
8/25/2005 12:47:31 PM
Reply:

Similar Artilces:

Average formula where blank cells are counted as zeros
I am trying to write an average formula that takes into account the blank cells. I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), it is not treating the blanks as zeros so the answer is much higher than it should be. Should I do a logic formula within the cells? If you know the range, you could do the division yourself: =sum(a1:a6)/6 But this would include other non-numeric cells in the count of cells (6), too. krwelling wrote: > > I am trying to write an average formula that takes into account the blank > cells. > > I ha...

Using variable names for cells
I seem to remember a technique where I could assign a variable name to the contents of a cell so that whenever I wanted to use the contents, all I had to do was call up the variable name. Unfortunately I cannot find the way to set up the process. Any suggestsions or ideas would be appreciated. Thanks and a Happy New Year. -- Take out the trash to reply '05 FLHTCUI Hi dim rng as range set rng=activesheet.range("A1") msgbox rng.value -- Regards Frank Kabel Frankfurt, Germany Ultraglide wrote: > I seem to remember a technique where I could assign a variable name to >...

How do I use excel names with INDIRECT with charts
Hello, I want to create a Chart that does not directly reference cell-ranges (i.e. A1:A6), but excel-names that make the reference sheet-independent. My aim is to be able to copy one chart to other worksheets, which have their dataareas at the same places like the source-sheet. Problem: This works fine in cells but not in charts Excel name definition: =INDIRECT("R3C2";0):INDIRECT("R3C5";0) Any idea about this? Thanks in advance, Holger. You have to include the sheet name in the final formula that you want XL to use. -- Regards, Tushar Mehta www.tushar-mehta.co...

SBS2003 + Internet Explorer
We have a customer who has an SBS 2003 Premium server with ISA 2004 They have just installed a bolt on to Outlook which searches their mailboxes or something. They have discovered that they cannot get this to work unless the untick the 'Use automatic configuration script' in Internet Explorer. Trouble is each time they re-boot a PC the tick comes back, so sounds like it's a policy setting somewhere. What is this ? Where is it configured and what are the implications of turning it off ? Could it be that rather than turn it off whatever it is ought to be con...

to extract multiple values from an array
Hi, I am into Image processing industry, for each job we create unique code in excel, we Process 20 jobs in a day, I want to list all the job Code in "summary of the day sheet" that we complete for the particular day . On Dec 30, 4:56=A0am, ratan h <ratha...@nextgenalbums.com> wrote: > Hi, > > I am into Image processing industry, for each job we create unique > code in excel, we Process 20 jobs in a day, I want to list all the job > Code in "summary of the day sheet" that we complete for the particular > day . Not enough info but, assuming you are...

Am I able to manage multiple email accounts
I have several email accounts including Gmail, HOtmail and corporate. I can't make Entourage mail (2004) list individual accounts or find how to manage profiles. Apple mail works but doesn't allow me to insert a tiff company logo without making it an attachment. Outlook allows for all of the above.<br> Appreciate help > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3285235088_9822952 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit On 2/7/08 1...

can cvs files be opened using excel viewer
i have a user who cannot open cvs files with excel viewer but can ope with the full version of excel...anyone experienced this problem and i so, can you pls post the resolution -- darrie ----------------------------------------------------------------------- darriel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2388 View this thread: http://www.excelforum.com/showthread.php?threadid=37522 There is nothing in the description of the Excel Viewer to suggest that it can read anything other than true XLS files. http://support.microsoft.com/default.aspx?scid=kb;...

Manufacturing BOM Where Used query
Winthin manufacturing there is a where-used inquiry window that will show all BOMs that a given item appears in. You can also expand that view to see what BOMs and item's parent is used in. Some of our BOMs are 10 layers deep. In our environment we need to know which category of finished goods each purchased item is used in. Currently we maintain this manually and store the info in an inventory user-defined field. Over time as the number of our finished goods and purchased parts has increased this has become an unmangeable process. I need to find a way to query the BOM tables a...

Configure single Outlook 2003 Account to try multiple outgoing servers?
Is there a way to configure a single Outlook 2003 account to try different SMTP servers for Outgoing mail? I log in from two different locations, and both are behind firewalls. And from each location I can't get to the other SMTP server to send my mail. To get around this, I've created two Outlook accounts to use. One for each location. But this is not a good solution, because I have to remember to use the correct account, and it's easy to make a mistake. ....When I do, the outgoing mail very quietly sits in my Outbox, and doesn't get sent. People get very upset at me for ...

How to prevent the same program from running twice using vc?
Try this: http://www.codeguru.com/Cpp/misc/misc/article.php/c299 -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "LeeTow" <fbjlt@pub3.fz.fj.cn> wrote in message news:#gTQPBglEHA.2612@TK2MSFTNGP15.phx.gbl... > > Checkout Bob's tip on this subject at http://bobmoore.mvps.org/Win32/w32tip7.htm -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "LeeTow" <fbjlt@pub3.fz.fj.cn> wrote in message news:%23gTQPBglEHA.2612@TK2MSFTNGP15.phx.gbl... > > See also my essay on my MVP Tips site. joe On Wed, 8 Sep 2...

How do I count the number of times a particular charcter ("." say)
How do I count the number of times a particular charcter ("." say) appears in a cell. For example, ABC.1.21.31.2 should retun 4. Is there a formula? =LEN(A1)-LEN(SUBSTITUTE(A1,".","")) -- HTH RP (remove nothere from the email address if mailing direct) "RH" <RH@discussions.microsoft.com> wrote in message news:75B61958-5352-436D-94F1-DD3D79AAE485@microsoft.com... > How do I count the number of times a particular charcter ("." say) appears in > a cell. > > For example, ABC.1.21.31.2 should retun 4. > > Is there ...

CRM for international companies
Hi, Being an international company, our pricelists are in different currencies. MS CRM stores the currency on the Organization level and not on the Business Unit level, which essentially prevents our use of it. (1) Any suggestions for a way to overcome this problem (besides "translating" the EU, EMEA and US pricelist to one currency, e.g. dollars, or waiting for CRM 2.0) ? (2) I noticed that the database schema contains "Organization ID" for all basic tables. This probably means that multiple organizations can run on the same CRM server. Currently, I can't se...

Utilizing the Print Preview without using a printer
How can I use the Print Preview function and make any necessary changes to my worksheet?..I currently do not have a printer installed, but would still like to be able to have access to the Print Preview functions before I actually print my worksheet. Just install *any* print driver that might be resident on your system, and XL will then enable the "PrintPreview" feature, even though no actual printer is present. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ==========================================...

Multiple email accounts #11
I use Outlook 2003 with multiple accounts (4 POP accounts and one Exchange account) and I have the following problems: Most of the time I am not connected to the corporate network, so most of the time I have no connection to the Exchange server. Because of this Outlook shows the dissconected icon on the bottom right status bar. Once Outlook says that it is dissconected from the Exchange server it doesn't do the automatic syncs with the POP accounts through the internet (which is indeed available). Is there a way to make Outlook autosync with the POP accounts if it is not conne...

Multiple Exchange boxes in one domain
Hi all, I have the following setup for my domain: HQ: Server 2003 Exchange 2003 We have a branch office located on another industrial estate 1/2 mile away, and we currently connect to them via WiFi (11Mb) and they are in the same domain and subnet as us. I am looking to add a second exchange box at their site so they can get their mail "locally". When I look at setting it up tho ot seems that exchange will still send the messages across the WiFi due to there only being 1 MX record? Is there a way I can get them to recive their mail directly over their own Broadband link? Also ...

Microsoft Office X cannot start because MS Office is already in use
Sometimes Entourage 10.1.4 shows the message: "Microsoft Office X cannot start because MS Office is already in use. A Office program is being used by MYNAME. Your installation exceeds..." It's crazy because I'm the only user with OSX (10.3.2) and Office X is installed only on my Mac!!! It seems that it happens when Entourage run scheduled actions (i.e. Send and Receive). Pls help me. Thanx. -- Buz ...

Using a value from a cell inside a formula!
Hi! I've got two cells that contain the start and end row of a matrix in sheet. I want to use these row numbers in a =COUNT.IF formula to count th number of specified instances in this matrix. But my problem is this; how can I use the values in the two cell inside the COUNT.IF formula?? I should look something like this; =COUNT.IF(F"cell1":F"cell2";"=argument") As you can see, the column (F) is specified in the formula but the ro number needs to be fetched from cell1 and cell2. Can anyone help me with the correct syntax? Thanks! -M -- marsupilam --...

Filter to specify which account is used according to email address?
I use outlook for work and personal email but i use two different accounts to send/recive email. I have run into the problem of sometimes mistakenly using my personal account to send work emails. So my question is: Is it possible to specify that addresses that meet a certain criteria be send using a certain account. Say an email to Bob@amarc.mil will be sent using my gmail account whereas a email to Jane@moog.gov will be sent using a yahoo account? Any help/suggestions would really be appreciated! Cheers -Gaiko And my question is, what version of Outlook are you using? The answer = is t...

IE crashing when using CRM 3
some users are experiencing a lot of IE crashes when using CRM3.0.5300.0 This is when clicking around the CRM and I have a screenshot of the CRM as it crashed if anyone needs this... The diagnostic is below: <?xml version="1.0" encoding="UTF-16"?> <DATABASE> <EXE NAME="iexplore.exe" FILTER="GRABMI_FILTER_PRIVACY"> <MATCHING_FILE NAME="HMMAPI.DLL" SIZE="38912" CHECKSUM="0xD85D870C" BIN_FILE_VERSION="6.0.2900.2180" BIN_PRODUCT_VERSION="6.0.2900.2180" PRODUCT_VERSION="6.00....

Terms of Use
Can someone please tell me, if someone bought a copy of Pub. 2002 (in a non-profit org.) and installed it on their machine, could anoher person in the same building install and use the same copy on their machine under the license? These two people would be using the program at times but not constantly - if they never used it simultaniously would it be allowed under the license? Thanks. No. (Non-profit status doesn't change anything.) -- JoAnn Paules MVP Microsoft [Publisher] "Christina" <anon@ymous.com> wrote in message news:857C024B-CF2D-4CE6-9E28-9AF2E9...

I can not use the Templates downloaded from Office Online
My Visio Version is 2002. I find that the Templates provided on the Office Online are for Office 2003.When I tried to open the Visio templates downloaded with Visio 2002,error occured. Any template available for Visio 2002?Where can I get it? Thanks in advance Microsoft has an interest in pushing the 2003 content. Perhaps you have a friend with 2003 that can save them in the older format for you? You might lose some features of the templates, but it will work to some extent. -- Hope this helps, Chris Roth Visio MVP "�췽" <cnpcpnnpc@hotmail.com> wrote in message n...

distribution lists sending out multiple mails
Hi, This a rather odd problem. My client has a distribution list setup with about 50 members. Sometimes when he sends an e-mail through the list some of the members receive 5 of the same e-mail. This does not happen to all of the members, only some of them. And the results are not consistent with everytime he sends an e-mail through the list. Just wondering if anyone has seen this. Possibly it's a problem on the recipients end. Any ideas? I am experiencing the same problem in Outlook 2003 with messages sen from a bcc list. Did you receive any answers that might be helpful t...

CString extension class causes proplems if it's used as an argument in sprintf(...)
Hi, I created my own CString class called CMyString which is derived form MFC's CString. Everything works fine except I use it as an argument in a sprintf(...) function or each other function which have a variable argument list. If I pass an instance of my class CMyString to sprintf(...) I have to cast it explicitly with (LPCTSTR) or (CString) to get the correct result. But if I pass an instance of CString I don't have to cast it. Do anybody know what's going on there? ThanX, Markus See my code below: ////////////////////////////////////////////////////////////////////////...

Multiple recipients #2
When mailing to multiple recipients, I want only their name to appear, but once they print, keep each recipients' addresses blocked. How can this be done without sending to myself and all other recipients a BCC? Mail merge. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, stellar asked: | When mailing to multiple recipients, I want only their name to | appear, but once they print, keep ea...

fetching using fetchxml from Javascript problem
var xml = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" + "<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" + "<soap:Body>" + "<fetchXml xmlns=\"http://schemas.microsoft.com/crm/2006/WebServices\">" + "<fetch mapping='logical'>" + "<entity name='inv...