Counting how many records have writing in two columns.
I am trying to finish a report that is to include a current calculation of: # of total employees, # of hourly employees, # of salary employees, # of employees on leave, # of current employees (not on leave), and finall (the part I am having trouble with), # of salary employees on leave and current salary employees as well as current hourly employees and hourly employees on leave. I have four columns in my table and query I am linking from (hourly, salary, LOA (means they on on leave), and schedule (means they are current). For the first few calculations I just had it count the number of hourly (anyone who has something written in the hourly column, et.c) I now want to count how many people have writing or text in the salary column AND the leave column. Or the hourly column AND the current column. What expression do I use to count the data that meets both criteria?
|
0 |
|
Utf
8/22/2007 10:18:00 PM
access.reports
4434 articles. 
0 followers.
1 Replies
623 Views
To count the number of records in a report where both FieldA and FieldB are not null: =Sum(Abs( Len([FieldA] & "")>0 and Len([FieldA] & "")>0)) -- Duane Hookom Microsoft Access MVP "RCF217" wrote: > I am trying to finish a report that is to include a current calculation of: > > # of total employees, # of hourly employees, # of salary employees, # of > employees on leave, # of current employees (not on leave), and finall (the > part I am having trouble with), # of salary employees on leave and current > salary employees as well as current hourly employees and hourly employees on > leave. I have four columns in my table and query I am linking from (hourly, > salary, LOA (means they on on leave), and schedule (means they are current). > For the first few calculations I just had it count the number of hourly > (anyone who has something written in the hourly column, et.c) I now want to > count how many people have writing or text in the salary column AND the leave > column. Or the hourly column AND the current column. What expression do I use > to count the data that meets both criteria?
|
|
0 |
|
Utf
8/23/2007 2:48:00 AM
Similar Artilces:
Counting paired data
I have a set of data with columns alternating names and numbers. I would like to return the number of times a specified name and number is paired up. For example, how many times is "Jones" in the data range with "30" in the cell to the right. I have tried OFFSET, COUNTIF, and SUMPRODUCT formulas, but have been unable to get the results I want. Any help would be appreciated. An array formula would be fine. Using Excel 97. Ken Schmidt Hi Ken Provided names are in column A and numbers in column B, formula: =SUMPRODUCT((A1:A50="Jones")*(B1:B50=30)) NB: This is...
Pivot Table and adding a % column, that is not in original data
Hi, Is it possible to add a column for % calculations when the % column is not in original data? To clarify, my original data is as follows: Produt Sales Returns Date A 5 June B 6 June A 1 July A 1 September B 1 November When I run the pivot table, one of the columns I'm then looking to get is a total % of returns over sales , but I cant see how to include in a Pivot table. I can add it outside of the table, but that has problems ...
convert columns to rows & rows to columns
Help! I need to change the format of sheet3 of a workbook, making th rows into columns and the columns into rows. This worksheet has formul links with the other sheets in that workbook, as well as formula link in other workbooks. How can this be done and still maintain th integrity of the information being imported automatically from th other sheets and workbooks? Can it be done -- ROCKWARRIO ----------------------------------------------------------------------- ROCKWARRIOR's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2709 View this thread: http://www.excelf...
Unhide a column
For whatever reason I cannot unhide a column in Excel 2000 after I have hidden it. I follow the instructions...........protection is turned off.....I select the columns on either side of the hidden column by holding control and selecting each column..........I right click after they are selected and there is no unhide option......so I go to the format menu...............select column and unhide and nothing happens. Any help here????? Thanx Greg Maybe the column is still there but very narrow.....only one pixel width? Have you tried to just move through the cells from left to right? If th...
How to separate variables within a column
Hi I need help. I have want to separate one column into two. But I don't want to sort or filter, I want to make two extra columns, so I can view the data side by side, how do I do it? eg make A 2 x A 2 y B 2 x B 2 y into x y A 2 2 B 2 2 A Pivot Table would easily do what you want..... With A1:C5 containing these values Letter Amount Category A 2 x A 2 y B 2 x B 2 y Then....from the Excel main menu: <Data><Pivot Table> Use: Excel Select your data: A1:C5 Click the [Layout] button ROW: Drag the Letter field here COLUMN: Drag the Category field here DATA:...
Context Menu with Bitmaps of Row and Column format
Hai Experts, I Want to have a Context menu with bitmaps(not text correspoding to that bitmap) displayed in Row and Column format , any ideas or links are appreciated and its just like a Messager "Smilyes" Button where in when i click on that button it displays some bitmaps in Row and Column format and please let me know about your sugestions Thanks and Regards suresh ...
PLEASE HELP: Assign column names to variables
Hi All, I have designed a form that searches from the current database based on the criteria that user selects(criterias are column names in the database table ) and clicks "search" button. Right now I have all column names (criterias in search form) hardcoded in the Select statement. But, I want the column names to be dynamic. What I mean is, I want to display columns that the users select in the form and not hardcode it. How can I assign column names to a variable? Thanks in advance Create a combo box (or list box), set its RowSourceType property to "Field ...
VBA?Macro Newbie Question//Last field in a column with a value.
I am a newbie to programming Excel. I Have a workbook which has a average about 40 worksheets. I keep individual attendance and payment for my Alcohol and Drug Treatment program on these worksheets. Th individual worksheets are linked to a master roster worksheet in different workbook. My question for today is how can I link the last payment in a column i an individual worksheet to the master roster so I can tell at a glanc when a person made their last payment? Also, how could I link th appropriate cells to the master without doing it manually every time enter a new client? Thanks ...
Counting unique values #2
Hi there! I am trying to count the number of unique referrals for numerous agencies. Using the table below.... Agent....Ref 1..........1 1..........1 2..........1 2..........2 2..........2 3..........1 4..........1 4..........1 4..........2 4..........2 4..........3 ...the result should be... 1..........1 2..........2 3..........1 4..........3 How can this be done? I have over 7000 entries to process! I've tried filtering and Pivot Tables, both without success. Any pointers would be gratefully received! Many thanks, Juerg -- jurgmay ---------------------------------------------...
Multiple Information in Columns
I have mutliple information in my columns for Access 2003. Here is an example of what I have Report # Worksheet Location Text 3055 A103 XYZ COmpnay 3055 A102 1231 Walnut St 3055 A104 Newark 3055 A105 NJ 3056 A103 ABC Company 3056 A102 456 Fultondale Ave 3056 A104 Twin City 3056 A105 ...
Column formatting not accepted
Good morning, I regularly export from Quickbooks to Excel 2000. I create a primary key 1,2,3,4, in the first column in order to assist with some sorting that I wish to complete. When finished I sort by the primary key which now lists as follows 1,10,11...2,20,21 - I have changed the formatting of the column with no luck - Any advice much appreciated - Glen It's not enough to just reformat the column. Try formatting the column as General then select an empty cell Edit|copy select your column again and edit|paste special|and Check Add (under operation) millarg wrote: > > Good mo...
Mailbox count
We have several stores per site on 3 separate servers all running Exchange 2003 SP1 on Windows server 2003. Exchange 5.5 admin gave you a way to find out the total number of mailboxes on a particular server. I have not found any way using System Manager or perfmon, can someone tell me how this is accomplished? Thanks Mike "Mike Y." <Mike Y.@discussions.microsoft.com> wrote: >We have several stores per site on 3 separate servers all running Exchange >2003 SP1 on Windows server 2003. Exchange 5.5 admin gave you a way to find >out the total number of mailboxes on...
Two orientations on one page
I'm using Publisher 2003 to create part of my wedding announcements. The document is about 1/4 page in size, so there are 4 per page. Across the top of each piece there is going to be some cutting done with a special die cutter. As such, it would be much easier if the two bottom quarters were upside down, so that the bottom edge of the page was in fact the top edge of the bottom two announcements. Hopefully that makes sense... How can I do this in publisher - that is, how can I print four per page (got that part already) so that the bottom two are upside-down (can't seem to ...
Question about an xpath depending on two xml nodes
Hi, in my case I've got the following XML document: <doc> <item> <foo>1</foo> <bar>a</bar> </item> <item> <foo>2</foo> <bar>b</bar> </item> </doc> My question is, if it is possible to read out the value of a child if another childnode has a specific value. In my example I want to read out the value of the foo node when the value of the bar node is an 'a'. Is this possible? Thanks in advance Martin Martin Horst wrote: > My question is, if it is possible to ...
Column length
Hi I'm going to fill out an excel sheet that later will bee converted to other program. To get the converting right each cell needs to have certain length (same length for the entire column). Like column A should have 5 characters and column B 15 characters. Some times a cell will bee empty but it should occupy 5 characters (so column C will start at character (5+15) no 20). Some columns will bee with numbers and some will bee with text. Thanks Regards Ove Malde You have a few choices (try against a copy of your worksheet): I'd either concatenate the cell values into another co...
Column widths get changed when another user opens my files
hello, i save an excel file on a network drive and when another user opens it the width of the columns are all changed. what's going on? How can we get stop this? thanks!!! ...
How do I change the row/column format in a macro to beyond letters?
Hi all, I recorded a very long VBA macro in Excel and the index of ranges is in letter format, for example, "M14:M19", etc. Now I am going to run this macro programmatically and automatically in a for loop and expand it from the left to the right so I want to change the "M" in the above example automatically. But after 26 letters, there will be AA, AB, etc. which is really hard to program. Is there a way to adapt the recorded macro (by changing as little as possible) to more than 26 letters. I really want to change as little as possible because I spent lots of time re...
calculate how many 'work days' in a month / week
hi guys, currently i have a formula (thanks to this NG) that calculates how many days there are in a month. is there a way to extend this formula to calculate the number of work days (mon-fri) there are in that month? cheers Use the NETWORKDAYS function: =NETWORKDAYS(start_date,end_date) -- Biff Microsoft Excel MVP "Chuck" <cvanoosbree@gmail.com> wrote in message news:1194478027.357664.102700@t8g2000prg.googlegroups.com... > hi guys, > > currently i have a formula (thanks to this NG) that calculates how > many days there are in a month. is there a way to ...
Counting cells with a value in them
We are running an "If" equation on a column to determine if an event i taking too long to do. The resulting equation will yield an "X" if i is taking to long and a " " if not. I would like to have an equatio calculate the number of "X" in the column. Any ideas? Chris Nelso -- chris ----------------------------------------------------------------------- chrisn's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=650 View this thread: http://www.excelforum.com/showthread.php?threadid=31880 We had a brain cramp and one of th...
Print problem with columns
I have inherited a spreadsheet and need to print it on one page. All the information is 2 columns. Is there a way to get the columns to wrap on a page so instead of having 3 pages with 2 columns each, I can have one page with six columns? There is no direct method... If you can print, say 50 rows per page, then you can do this enter in C1 =A51 and copy one col right and 50 rows down... If you are still left with values then enter in E1 =A101 and repeat the above Print only rows 1-50... -- Always provide your feedback so that others know whether the solution worked or problem still pe...
Counting colored cells?
Group, Does anyone know a way if I can do something like a SUMIF() based on the background color of cells? For example, if I have data and I color the cells in column individually, and I want to sum the contents of all the green cells, can I do that? Furthermore, can I do a COUNTIF() to count the number of cells with a given color? Thanks, Mike If cells are colored due to Conditional Format then use the CF criteria to count. If colored manually, you must use a VBA Function. See Chip Pearson's site for this. http://www.cpearson.com/excel/colors.htm Gord Dibben MS Excel MVP On 1...
Organize Columns in Inbox, Specifically Flag Status
Hello, I would like the Flag Status column to appear after the Icon column in my Inbox view. (It is currently the last column.) In the Show Fields dialog box, Flag Status appears as the third item, after Importance and Icon, which is what I want but it doesn't appear in this order in my Inbox window. I'm using Outlook 2003. Any suggestions? You need to disable quick flags if you want to move the column. It's in Other Settings in the Current view menu. See http://www.outlook-tips.net/beginner/viewmenu.htm if you need help finding the menu. -- Diane Poremsky [MVP - Ou...
Account Address on Case Record
Hello All, We are currently working with Cases and want to have the ability to pull address information from the account/contact record directly onto the case. Has anyone done this before or have any suggestions for achieving this functionality. Ideally, we would have the Lookup Address similiar to the Quote and Order. Thanks for the help! ...
IM 9.0
We recently upgraded from GP 7.0 to GP 9.0. We use Ceridian for payroll and have an integration that takes a text file it creates and imports it into the GL. The text file is in account number order. The text file is used as two integration sources, one source groups the entries to create the journal header and the other source returns all the records from the file sorted by Account Number. There is a grouping that joins the two sources. The GL batch is in a random order. While technically this is not a problem the resulting reports are in that same random order which makes them d...
How do I count items based on multiple criteria from a different worksheet?
I have tried {=SUM(('10-16-2005'!F:F="Brentwood") * ('10-16-2005'!B:B="ACTV"))} (entered with F2, then cntrl/shift /return, but it is not returning what It should. What I would REALLY like to do, is if the first letter of column B (STATUS) starts with a "A", "B" or "N" .AND. Column F (City) = "Brentwood" .AND. Column J (BT) = "DE". All of these are on Worksheet 10-16-05 (or preferrably, what ever the column header is on SHEET1, row1, column()) Thanks! Mc Here is a formula =SUMPRODUCT((ISNUMBER(FIN...
I have a set of data with columns alternating names and numbers. I would like to return the number of times a specified name and number is paired up. For example, how many times is "Jones" in the data range with "30" in the cell to the right. I have tried OFFSET, COUNTIF, and SUMPRODUCT formulas, but have been unable to get the results I want. Any help would be appreciated. An array formula would be fine. Using Excel 97. Ken Schmidt Hi Ken Provided names are in column A and numbers in column B, formula: =SUMPRODUCT((A1:A50="Jones")*(B1:B50=30)) NB: This is...
Pivot Table and adding a % column, that is not in original data
Hi, Is it possible to add a column for % calculations when the % column is not in original data? To clarify, my original data is as follows: Produt Sales Returns Date A 5 June B 6 June A 1 July A 1 September B 1 November When I run the pivot table, one of the columns I'm then looking to get is a total % of returns over sales , but I cant see how to include in a Pivot table. I can add it outside of the table, but that has problems ...
convert columns to rows & rows to columns
Help! I need to change the format of sheet3 of a workbook, making th rows into columns and the columns into rows. This worksheet has formul links with the other sheets in that workbook, as well as formula link in other workbooks. How can this be done and still maintain th integrity of the information being imported automatically from th other sheets and workbooks? Can it be done -- ROCKWARRIO ----------------------------------------------------------------------- ROCKWARRIOR's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2709 View this thread: http://www.excelf...
Unhide a column
For whatever reason I cannot unhide a column in Excel 2000 after I have hidden it. I follow the instructions...........protection is turned off.....I select the columns on either side of the hidden column by holding control and selecting each column..........I right click after they are selected and there is no unhide option......so I go to the format menu...............select column and unhide and nothing happens. Any help here????? Thanx Greg Maybe the column is still there but very narrow.....only one pixel width? Have you tried to just move through the cells from left to right? If th...
How to separate variables within a column
Hi I need help. I have want to separate one column into two. But I don't want to sort or filter, I want to make two extra columns, so I can view the data side by side, how do I do it? eg make A 2 x A 2 y B 2 x B 2 y into x y A 2 2 B 2 2 A Pivot Table would easily do what you want..... With A1:C5 containing these values Letter Amount Category A 2 x A 2 y B 2 x B 2 y Then....from the Excel main menu: <Data><Pivot Table> Use: Excel Select your data: A1:C5 Click the [Layout] button ROW: Drag the Letter field here COLUMN: Drag the Category field here DATA:...
Context Menu with Bitmaps of Row and Column format
Hai Experts, I Want to have a Context menu with bitmaps(not text correspoding to that bitmap) displayed in Row and Column format , any ideas or links are appreciated and its just like a Messager "Smilyes" Button where in when i click on that button it displays some bitmaps in Row and Column format and please let me know about your sugestions Thanks and Regards suresh ...
PLEASE HELP: Assign column names to variables
Hi All, I have designed a form that searches from the current database based on the criteria that user selects(criterias are column names in the database table ) and clicks "search" button. Right now I have all column names (criterias in search form) hardcoded in the Select statement. But, I want the column names to be dynamic. What I mean is, I want to display columns that the users select in the form and not hardcode it. How can I assign column names to a variable? Thanks in advance Create a combo box (or list box), set its RowSourceType property to "Field ...
VBA?Macro Newbie Question//Last field in a column with a value.
I am a newbie to programming Excel. I Have a workbook which has a average about 40 worksheets. I keep individual attendance and payment for my Alcohol and Drug Treatment program on these worksheets. Th individual worksheets are linked to a master roster worksheet in different workbook. My question for today is how can I link the last payment in a column i an individual worksheet to the master roster so I can tell at a glanc when a person made their last payment? Also, how could I link th appropriate cells to the master without doing it manually every time enter a new client? Thanks ...
Counting unique values #2
Hi there! I am trying to count the number of unique referrals for numerous agencies. Using the table below.... Agent....Ref 1..........1 1..........1 2..........1 2..........2 2..........2 3..........1 4..........1 4..........1 4..........2 4..........2 4..........3 ...the result should be... 1..........1 2..........2 3..........1 4..........3 How can this be done? I have over 7000 entries to process! I've tried filtering and Pivot Tables, both without success. Any pointers would be gratefully received! Many thanks, Juerg -- jurgmay ---------------------------------------------...
Multiple Information in Columns
I have mutliple information in my columns for Access 2003. Here is an example of what I have Report # Worksheet Location Text 3055 A103 XYZ COmpnay 3055 A102 1231 Walnut St 3055 A104 Newark 3055 A105 NJ 3056 A103 ABC Company 3056 A102 456 Fultondale Ave 3056 A104 Twin City 3056 A105 ...
Column formatting not accepted
Good morning, I regularly export from Quickbooks to Excel 2000. I create a primary key 1,2,3,4, in the first column in order to assist with some sorting that I wish to complete. When finished I sort by the primary key which now lists as follows 1,10,11...2,20,21 - I have changed the formatting of the column with no luck - Any advice much appreciated - Glen It's not enough to just reformat the column. Try formatting the column as General then select an empty cell Edit|copy select your column again and edit|paste special|and Check Add (under operation) millarg wrote: > > Good mo...
Mailbox count
We have several stores per site on 3 separate servers all running Exchange 2003 SP1 on Windows server 2003. Exchange 5.5 admin gave you a way to find out the total number of mailboxes on a particular server. I have not found any way using System Manager or perfmon, can someone tell me how this is accomplished? Thanks Mike "Mike Y." <Mike Y.@discussions.microsoft.com> wrote: >We have several stores per site on 3 separate servers all running Exchange >2003 SP1 on Windows server 2003. Exchange 5.5 admin gave you a way to find >out the total number of mailboxes on...
Two orientations on one page
I'm using Publisher 2003 to create part of my wedding announcements. The document is about 1/4 page in size, so there are 4 per page. Across the top of each piece there is going to be some cutting done with a special die cutter. As such, it would be much easier if the two bottom quarters were upside down, so that the bottom edge of the page was in fact the top edge of the bottom two announcements. Hopefully that makes sense... How can I do this in publisher - that is, how can I print four per page (got that part already) so that the bottom two are upside-down (can't seem to ...
Question about an xpath depending on two xml nodes
Hi, in my case I've got the following XML document: <doc> <item> <foo>1</foo> <bar>a</bar> </item> <item> <foo>2</foo> <bar>b</bar> </item> </doc> My question is, if it is possible to read out the value of a child if another childnode has a specific value. In my example I want to read out the value of the foo node when the value of the bar node is an 'a'. Is this possible? Thanks in advance Martin Martin Horst wrote: > My question is, if it is possible to ...
Column length
Hi I'm going to fill out an excel sheet that later will bee converted to other program. To get the converting right each cell needs to have certain length (same length for the entire column). Like column A should have 5 characters and column B 15 characters. Some times a cell will bee empty but it should occupy 5 characters (so column C will start at character (5+15) no 20). Some columns will bee with numbers and some will bee with text. Thanks Regards Ove Malde You have a few choices (try against a copy of your worksheet): I'd either concatenate the cell values into another co...
Column widths get changed when another user opens my files
hello, i save an excel file on a network drive and when another user opens it the width of the columns are all changed. what's going on? How can we get stop this? thanks!!! ...
How do I change the row/column format in a macro to beyond letters?
Hi all, I recorded a very long VBA macro in Excel and the index of ranges is in letter format, for example, "M14:M19", etc. Now I am going to run this macro programmatically and automatically in a for loop and expand it from the left to the right so I want to change the "M" in the above example automatically. But after 26 letters, there will be AA, AB, etc. which is really hard to program. Is there a way to adapt the recorded macro (by changing as little as possible) to more than 26 letters. I really want to change as little as possible because I spent lots of time re...
calculate how many 'work days' in a month / week
hi guys, currently i have a formula (thanks to this NG) that calculates how many days there are in a month. is there a way to extend this formula to calculate the number of work days (mon-fri) there are in that month? cheers Use the NETWORKDAYS function: =NETWORKDAYS(start_date,end_date) -- Biff Microsoft Excel MVP "Chuck" <cvanoosbree@gmail.com> wrote in message news:1194478027.357664.102700@t8g2000prg.googlegroups.com... > hi guys, > > currently i have a formula (thanks to this NG) that calculates how > many days there are in a month. is there a way to ...
Counting cells with a value in them
We are running an "If" equation on a column to determine if an event i taking too long to do. The resulting equation will yield an "X" if i is taking to long and a " " if not. I would like to have an equatio calculate the number of "X" in the column. Any ideas? Chris Nelso -- chris ----------------------------------------------------------------------- chrisn's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=650 View this thread: http://www.excelforum.com/showthread.php?threadid=31880 We had a brain cramp and one of th...
Print problem with columns
I have inherited a spreadsheet and need to print it on one page. All the information is 2 columns. Is there a way to get the columns to wrap on a page so instead of having 3 pages with 2 columns each, I can have one page with six columns? There is no direct method... If you can print, say 50 rows per page, then you can do this enter in C1 =A51 and copy one col right and 50 rows down... If you are still left with values then enter in E1 =A101 and repeat the above Print only rows 1-50... -- Always provide your feedback so that others know whether the solution worked or problem still pe...
Counting colored cells?
Group, Does anyone know a way if I can do something like a SUMIF() based on the background color of cells? For example, if I have data and I color the cells in column individually, and I want to sum the contents of all the green cells, can I do that? Furthermore, can I do a COUNTIF() to count the number of cells with a given color? Thanks, Mike If cells are colored due to Conditional Format then use the CF criteria to count. If colored manually, you must use a VBA Function. See Chip Pearson's site for this. http://www.cpearson.com/excel/colors.htm Gord Dibben MS Excel MVP On 1...
Organize Columns in Inbox, Specifically Flag Status
Hello, I would like the Flag Status column to appear after the Icon column in my Inbox view. (It is currently the last column.) In the Show Fields dialog box, Flag Status appears as the third item, after Importance and Icon, which is what I want but it doesn't appear in this order in my Inbox window. I'm using Outlook 2003. Any suggestions? You need to disable quick flags if you want to move the column. It's in Other Settings in the Current view menu. See http://www.outlook-tips.net/beginner/viewmenu.htm if you need help finding the menu. -- Diane Poremsky [MVP - Ou...
Account Address on Case Record
Hello All, We are currently working with Cases and want to have the ability to pull address information from the account/contact record directly onto the case. Has anyone done this before or have any suggestions for achieving this functionality. Ideally, we would have the Lookup Address similiar to the Quote and Order. Thanks for the help! ...
IM 9.0
We recently upgraded from GP 7.0 to GP 9.0. We use Ceridian for payroll and have an integration that takes a text file it creates and imports it into the GL. The text file is in account number order. The text file is used as two integration sources, one source groups the entries to create the journal header and the other source returns all the records from the file sorted by Account Number. There is a grouping that joins the two sources. The GL batch is in a random order. While technically this is not a problem the resulting reports are in that same random order which makes them d...
How do I count items based on multiple criteria from a different worksheet?
I have tried {=SUM(('10-16-2005'!F:F="Brentwood") * ('10-16-2005'!B:B="ACTV"))} (entered with F2, then cntrl/shift /return, but it is not returning what It should. What I would REALLY like to do, is if the first letter of column B (STATUS) starts with a "A", "B" or "N" .AND. Column F (City) = "Brentwood" .AND. Column J (BT) = "DE". All of these are on Worksheet 10-16-05 (or preferrably, what ever the column header is on SHEET1, row1, column()) Thanks! Mc Here is a formula =SUMPRODUCT((ISNUMBER(FIN...