Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum and count text and numbers same column
I have a table used for attendance tracking of violations, using a list of
Alpha codes: L = late, A= Absent. I then count these text values by using Sumproduct to evaluate a 12 month period of time, based on the hire date of the employee, with a current column of dates. In the same column, I want to place numeric values to add up attendance hours, using the same rolling 12 month period, forward of the anniversary date. Dan Smith anniversarry date 1/1/2009 Today is 4/30/2009 Date 1/1/2009 A (this value represents "absent") 1/2/2009 1/3/2009 1/4/2009 8 (this value represents 8 hours vacation) 1/5/2009 1/6/2009 1/7/2009 1/8/2009 L (this value represents "late") ON the spread sheet there is an upper section that calculates in table how many hours of vacation are available based on tenure, in column Q. It ranges for 40 years. So if for example the employee has 5 years service, it will return a value of 80 hours, in Column Q row 32. Then to the right in column R, I accumulate the actual hours used, then in another cell, I calculate the balance. So if it sees a value in column Q, it will reference the list of dates in Column O, and sum the numbers in Column R, for a balance of vacation hours used. The < formulas compare their anniversary date to the dates in column O, and keep a running range based on the current date. My problem is this. I want to use Text and numbers in the same column, and when I use the formula below, I get #VALUE! If I keep the text and numbers in 2 separate columns, it all works just fine. Formula for obtaining the numeric vacation totals on running 12 month range: =IF(Q320,SUMPRODUCT((O$137:O$9000=O32)*(O$137:O$ 9000<O33)*R$137:R$9000),0) Formula for obtaining the text count for lates ("L"), on running 12 month range: m6 = 1 year ago from today =SUMPRODUCT((O$137:O$9002$M$6)*(Q$137:Q$9002="L") ) -- LJW |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum and count text and numbers same column
Try these:
=IF(Q320,SUMPRODUCT(--(O$137:O$9000=O32),--(O$137:O$9000<O33),R$137:R$9000),0) =SUMPRODUCT(--(O$137:O$9002$M$6),--(Q$137:Q$9002="L")) -- Biff Microsoft Excel MVP "Babylynn" wrote in message ... I have a table used for attendance tracking of violations, using a list of Alpha codes: L = late, A= Absent. I then count these text values by using Sumproduct to evaluate a 12 month period of time, based on the hire date of the employee, with a current column of dates. In the same column, I want to place numeric values to add up attendance hours, using the same rolling 12 month period, forward of the anniversary date. Dan Smith anniversarry date 1/1/2009 Today is 4/30/2009 Date 1/1/2009 A (this value represents "absent") 1/2/2009 1/3/2009 1/4/2009 8 (this value represents 8 hours vacation) 1/5/2009 1/6/2009 1/7/2009 1/8/2009 L (this value represents "late") ON the spread sheet there is an upper section that calculates in table how many hours of vacation are available based on tenure, in column Q. It ranges for 40 years. So if for example the employee has 5 years service, it will return a value of 80 hours, in Column Q row 32. Then to the right in column R, I accumulate the actual hours used, then in another cell, I calculate the balance. So if it sees a value in column Q, it will reference the list of dates in Column O, and sum the numbers in Column R, for a balance of vacation hours used. The < formulas compare their anniversary date to the dates in column O, and keep a running range based on the current date. My problem is this. I want to use Text and numbers in the same column, and when I use the formula below, I get #VALUE! If I keep the text and numbers in 2 separate columns, it all works just fine. Formula for obtaining the numeric vacation totals on running 12 month range: =IF(Q320,SUMPRODUCT((O$137:O$9000=O32)*(O$137:O$ 9000<O33)*R$137:R$9000),0) Formula for obtaining the text count for lates ("L"), on running 12 month range: m6 = 1 year ago from today =SUMPRODUCT((O$137:O$9002$M$6)*(Q$137:Q$9002="L") ) -- LJW |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
assigning numbers to text and count the text | Excel Discussion (Misc queries) | |||
add 1 count to a column of numbers | Excel Worksheet Functions | |||
Count, numbers and text | Excel Discussion (Misc queries) | |||
how do I count the number of times text in column A matches text i | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |