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
641 Views

Similar Articles

[PageSpeed] 49

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:

Can I fade the edges of photos that I use?
I am trying to use photos in a publisher document that I want to fade the edges of, is this possible to do with publisher? -- D:o) krazy-4-coke wrote: > I am trying to use photos in a publisher document that I want to fade the > edges of, is this possible to do with publisher? Not really. You should use dedicated image manipulation software for this, such as Photoshop Elements, Paint Shop Pro, or Microsoft Digital Image. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org Ed, Are any of these programs typically come loaded on a computer?? I have another question - ca...

exchange 2003
I just set up a mail server at things are working fine in the network where it resides. The issue is that I have 5 other buildings that are connected via a VPN, and they can not connect. Do I need to change settings at their locations? DNS?? WINNS??? It depends on type of client, but generally proper DNS setup is required. Also make sure that no firewalls interfere. laker18 wrote: > I just set up a mail server at things are working fine in > the network where it resides. The issue is that I have 5 > other buildings that are connected via a VPN, and they > can not co...

Using different form strings to filter
Hello. Is there a way to code a query to have it use a filter string if a form is open, or ignore it if the filter is not open? Example: Query Name: Query1 Form: Form1 and Form2 Query fields: Field1 and Field2 I have a string in field 1 and 2 Forms!Form1!Field1 in the first field of the query, and Forms!Form1! Field2 in the second field. Can I also have Forms!Form2!Field1 in the query as well? I have tried this, but a box pops up and asks for the information from form 2 when it is not open. Thank you. hi, nybaseball22@gmail.com wrote: > Hello. Is there a way to code a query to...

Using Ghost as OS Backup
I attended a class a while back where the exchange admins said they used Ghost to clone the OS drive. I bought the product and it says that during the clone process it temporarily removes the server from the domain. Has anyone done this or had any problems? Thanks, Fred On Mon, 21 Nov 2005 05:26:28 -0600, "Fred T" <fredt2@mail.com> wrote: >I attended a class a while back where the exchange admins said they used >Ghost to clone the OS drive. I bought the product and it says that during >the clone process it temporarily removes the server from the domain. ...

using beforeprint
I am trying to get an application to append a file on a sheet printout. I am using Workbook_BeforePrint. It prints fine, but doesn't seem t execute the sub. What's wrong -- Message posted from http://www.ExcelForum.com Hi you may post your existing code :-) Also check that you have put this code in your workbook module ('ThisWorkbook') -- Regards Frank Kabel Frankfurt, Germany > I am trying to get an application to append a file on a sheet > printout. I am using Workbook_BeforePrint. It prints fine, but > doesn't seem to execute the sub. What's wrong?...

Do you need Access to use an Access Database for data entry only?
We have an Access database completed. Now we will use it for reading as well as for data entry. Do we need to install Access for every workstation needing the database for data entry or reading? No you don't necessarily need Access on every computer. You CAN use the Access Runtime and if it is in Access 2007 you can use it for free. If you are using another version then you would need to purchase the developer's edition to be able to legally distribute the associated Access runtime. If you do have 2007 and want to use the runtime, make sure your Full version of Access doe...

DPM sometimes uses too many tapes
Hi, i have problem with DPM long term backups on tape. I have collocation enabled so that is not a problem. Sometimes DPM uses too many tapes for same protection group, sometimes it uses 1, and then sometimes 2 - 4. Amount of data is always same. Can anybody help me please, what could be the problem? Hi Tornado, Can you explain protection group configurations as well as tape usage in detail, so that we can analyze on what is happening? Thanks, Jyothsna[MSFT] This posting is provided "AS IS" with no warranties, and confers no rights. "Tornado" <Torn...

is there any way I can trap for the use of navigation buttons?
Hi Is there any way I can trap for the use of navigation buttons, such as Next, Previous and New record? Stapes Probably. Trap what and when? "Stapes" <steve.staple@gmail.com> wrote in message news:7fe0fd6b-0da1-495e-a369-29bc72ca3af9@s8g2000prg.googlegroups.com... > Hi > > Is there any way I can trap for the use of navigation buttons, such as > Next, Previous and New record? > > Stapes On Wed, 16 Jan 2008 05:14:20 -0800 (PST), Stapes <steve.staple@gmail.com> wrote: No. And there shouldn't be a reason for that. There are plenty of events in...

Cannot stop using online services with First Tech Credit Union
My bank, First Tech Credit Union, recently upgraded their online banking services. This upgrade also included a change to the user name and password used for online banking in Money 2006. Their upgrade instructions (http://www.firsttechcu.com/help/help_guide_money.html) say to disable online services for your Money file as the third step to upgrading to the new services. The problem is that when I click on Stop Using Online Services in Money nothing happens. I click on OK and Money hangs for a bit then comes back with no change, clicking on OK again has the same effect, clicking on ...

Outlook 2002 multiple file opening
In Outlook 2000, CTRL A would select all attached picture files, then Open would, in my case, open the files in Paintshop Pro. With the 2002 version I cannot find a way to do this. Any suggestions? ...

Count occurrances with conditions
I am trying to count in 3 columns. Column A will have entries of "B or "S" and columns B and C will have "1" or "0". A B C B 0 1 B 1 0 B 1 1 S 1 1 I want to count how many times column C has a 1 with a B or S. That i no problem, I can do that. I also want to count the number of time column B has a 1 with a B or S. That is no problem, I can do that. However, the third count I want to count is when a 1 appears in bot column B and...

Using CComboBox embedded in CToolBar
Hi, I've derived a class from CToolBar and overridden OnCreate to replace 2 placeholder buttons with 2 combo boxes. The two combo boxes are also derived from CComboBox, and populate themselves with appropriate strings. My problem is that I'm not sure of how to inform my application when the user has selected a new item in one of the combo boxes. I've added a handler for the CBN_SELCHANGE message to the CComboBox, but I don't know what to put in it. What is the best way to forward the message to the mainframe? Also, when I replace the placeholder buttons, can I reuse their ...

Using paste in macro but with variable references.
Hi all, I am trying to use the paste function in a macro but it needs to be variable. I have split a list of data (team workloads) into the seperate teams and by days of the week. I now need the macro to compile one book with all the data in order. The headings for each team are already set on a worksheet so the data needs to be entered under each heading For example: Team A Day 1 under the heading "Team A Day 1", Team B Day 1 under the next heading on the same sheet "Team B Day 1". I hit problems as the workload ammount will alter from week to week. So of course asking...

if cell in other worksheet meets criteria, then leave blank.
I have data in worksheet A for each month, for each entity. Then I have a summary page which shows just the averages for each entity for each month. Then I need to create a master summary page which shows just the annual average for each entity, just one line per entity. My Summary page shows Jan-Dec in column A. I have a formula averaging the numbers from worksheet A. Jan-Mar is done. But the rest of the year hasn't happened yet, so April's formula results "#DIV/0!". I have already put in all the formulas for the year to be done with it. But I don'...

is there a way of using a second monitor as a customer display
This is a multi-part message in MIME format. ------=_NextPart_000_018F_01C86B1D.3EAD2F60 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, Does anyone know if rms works with 2 monitors. What I mean can you use a second monitor as customer display or something similar. A lot of pos systems out there use a second monitor as customer display, I m not sure if rms supports that option or not but any help would be appreciated. thanks ------=_NextPart_000_018F_01C86B1D.3EAD2F60 Content-Type: text/html; charset="us-ascii" Content-Transfer-...

Creating Charts for Use in Powerpoint
I have a series of charts that I need to create for a Powerpoin presentation. I thought it would be easy to just create the charts/graphs in Exce and then just cut and paste to the PP-Slides. I'm finding that it isn' quite that easy. Here are some of my problems that I hope someone might be able to she some light on and give me some direction. 1. In Excel how do you control the size of the chart? - When I create a chart 1 and define the data ranges I get a goo looking chart then I thouht, why reinvent the wheel so I just copy th chart to create chart 2 and change the data range, but...

Re:Help with verifying the signature of enveloped -message using x509 certificates.
Re:Help with verifying the signature of enveloped -message using x509 certificates. Hey Friends, I have an xml given by our partner trying to integrating with us and i have their certificate with public key installed in both personal and trusted people folders. when i am using the following code to check the signature of message signed by my certificate i am able to verify the signature but when i am trying to check the signature of the message of our partner using the same following code i am not able to verify the message. the message sent by our partner is right below the code.I wou...

How do I create a chart in Excel using data from two worksheets
I am trying to create a chart using data from two separate worksheets but it will only allow me to use the information from one. Here is one way: I made a chart from data in A10:B20 on sheet1 (A was the x-category data) I copied some cells from Sheet2, right clicked in chart area of chart and used Paste After the copy, I could have left clicked the chart and used Edit | Paste Special to specify more about the data I want to paste. Try it and come back with more questions. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "cdptuesdays" <...

Can't use my MS Office
I have recently purchased the above software package. I have not registered my product, as I have been travelling and it has=20 been less than 3 weeks since purchasing and installing. I am not sure=20 if all of the office programs have been working, as the only ones I have = been using these past 3 weeks are Entourage and MSN Messenger. =20 This morning, I tried to use Word and it froze just after opening the=20 new document and I was only able to enter one letter. Then I tried=20 excel and it froze at the first cell inside the new book. Then I tried = to=20 re-open my previously working E...

condional formating count question
Hi, I have a row in which I have a condition that if two numbers are identical its formating the 2nd number in Red Font, Is there a way or formula that I can use to count these red font Numbers? Thanks in advance for you help, It is best to use the same means in a formula that you used to make the font RED in the first place rather than trying to use a function to check the color of a cell. You can take a look at Chip Pearson's page, Functions For Working With Cell Colors http://www.cpearson.com/excel/colors.htm for counting cells in a range with involving normal cell coloring for ...

Multiple ship-to same customer
We have multiple ship-to accounts with single vendors, i.e. one of our customers has three offices that we drop ship supplies directly from the vendor. The vendors have set up specific account numbers for each singular office/location, i.e. any location we ship to has a unique customer number. We suspect if these individual account numbers are not indicated on our PO to the vendor since we fax many of our orders to the vendor (in addition to the ship-to address) that we will end up with orders shipped to the wrong office(s). How do we set up one vendor (to link all the items from tha...

Opening Word, the new doc says, "Document 1 -non-commerical use"
Every time I open a MS program (in Vista) the document pops up with the following at the top of the document, "Document 1 - non-commerical use only". Then what ever program I am in (Excel, Power Point, Word, etc) does not run, it crawls. Has anyone had a similar experience and found a fix? I have a sense it might have something to do with the language the program is in but have found not answers there either. Thanks for your assistance. On Sun, 7 Feb 2010 17:37:01 -0800, thedish <thedish@discussions.microsoft.com> wrote: >Every time I open a MS program (i...

Copy & paste in multiple areas using VBA
I'm trying to select a series of ranges to 1. paste a named formula I've created and then 2. remove the formula & leave the value What I have so far is shown below, but I'm sure there must be a better way. The problem seems to be that I can't use the copy function in a multiple range. I guess I can't paste xlValues to multiple ranges either, so I'm being forced to handle each range, one at a time, which seems rather cumbersome. Sub Macro1() Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122,G126:R134").Select Selection = "=ITNBu...

Counting Text Entries #2
Fantastic, thank you so much -- JackJJ ----------------------------------------------------------------------- JackJJW's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1625 View this thread: http://www.excelforum.com/showthread.php?threadid=27655 ...

Counting Consecutive Years
I'm trying to count the maximum number of consecutive years that an individual has made a gift to our institution. I have a range of 35 years to consider and all my information resides in one table. It doesn't matter when the last giving year was as long as s/he give 'x' number of consecutive years before that (with 'x' being a user-defined parameter). I will also want to know the minimum and maximum values for that range. I'm using Access 2003 and know very little Visual Basic. Any help would be appreciated. If the fields are like: Name YearContribution ...