Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMPRODUCT, 3 variables, 2 types of data in 1 column
I'm having trouble using SUMPRODUCT in the following situation:
Col A Col B Col C Col D Col E Name Ranges for Columns with Data to consider: EmpExp ExpType ExpOwner Rate 1stQtr Data: Personnel O WB $500,000 (formatted in 000's) Associated FTE $100 A formula calcs 1.2 Personnel O WF $500,000 (formatted in 000's) Associated FTE $100 A formula calcs 1.2 My formulas is =SUMPRODUCT(--(EmpExp="Associated FTE"),--(ExpType="O"),--(ExpOwner="WB"),1stQtr) I want to get 1.2. I want the formula to add only number of Associated FTE, not Employment Expense dollars. However, I'm getting 0. The data alternates rows like this all down the column - first dollars, then Associated FTE. Associated FTE is a calculated number. Employment Expense is entered. The number formats are different - Employment Expense is in 000's - $500,000 shows as $500. FTE is formated with one digit, so shows 1.2. Hope someone out there can help - I'm stumped. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMPRODUCT, 3 variables, 2 types of data in 1 column
It looks like your column B and column C entries are only alternately
present, so that on the row where you have Associated FTE they are both empty and you would need to refer to the previous row. One quick way of filling them with the data above is to highlight columns B and C, then press F5 (GoTo), click on Special, then select Blanks, and only the blank cells in those columns will then be highlighted. Then begin to enter a formula by typing =, press the up- arrow key once, and then do CTRL-Enter - this will fill the highlighted cells with the value from the row above. Then see if your formula works. Hope this helps. Pete On Feb 6, 10:25*pm, Dana M wrote: *I'm having trouble using SUMPRODUCT in the following situation: Col A * * * * * * * * Col B * * * * *Col C * * * * *Col D * * * Col E Name Ranges for Columns with Data to consider: EmpExp * * * * * * ExpType * * ExpOwner * Rate * * * *1stQtr Data: Personnel * * * * * * O * * * * * * WB * * * * * * * * * * * * * $500,000 (formatted in 000's) Associated FTE * * * * * * * * * * * * * * * * * * * $100 * * * A formula calcs 1.2 Personnel * * * * * * O * * * * * * WF * * * * * * * * * * * * * $500,000 (formatted in 000's) Associated FTE * * * * * * * * * * * * * * * * * * * $100 * * * A formula calcs 1.2 My formulas is =SUMPRODUCT(--(EmpExp="Associated FTE"),--(ExpType="O"),--(ExpOwner="WB"),1stQtr) I want to get 1.2. *I want the formula to add only number of Associated FTE, not Employment Expense dollars. *However, I'm getting 0. *The data alternates rows like this all down the column - first dollars, then Associated FTE. * Associated FTE is a calculated number. *Employment Expense is entered. *The number formats are different - Employment Expense is in 000's - $500,000 shows as $500. *FTE is formated with one digit, so shows 1.2. *Hope someone out there can help - I'm stumped. *Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMPRODUCT, 3 variables, 2 types of data in 1 column
Thanks, Pete! 100% helpful!
"Pete_UK" wrote: It looks like your column B and column C entries are only alternately present, so that on the row where you have Associated FTE they are both empty and you would need to refer to the previous row. One quick way of filling them with the data above is to highlight columns B and C, then press F5 (GoTo), click on Special, then select Blanks, and only the blank cells in those columns will then be highlighted. Then begin to enter a formula by typing =, press the up- arrow key once, and then do CTRL-Enter - this will fill the highlighted cells with the value from the row above. Then see if your formula works. Hope this helps. Pete On Feb 6, 10:25 pm, Dana M wrote: I'm having trouble using SUMPRODUCT in the following situation: Col A Col B Col C Col D Col E Name Ranges for Columns with Data to consider: EmpExp ExpType ExpOwner Rate 1stQtr Data: Personnel O WB $500,000 (formatted in 000's) Associated FTE $100 A formula calcs 1.2 Personnel O WF $500,000 (formatted in 000's) Associated FTE $100 A formula calcs 1.2 My formulas is =SUMPRODUCT(--(EmpExp="Associated FTE"),--(ExpType="O"),--(ExpOwner="WB"),1stQtr) I want to get 1.2. I want the formula to add only number of Associated FTE, not Employment Expense dollars. However, I'm getting 0. The data alternates rows like this all down the column - first dollars, then Associated FTE. Associated FTE is a calculated number. Employment Expense is entered. The number formats are different - Employment Expense is in 000's - $500,000 shows as $500. FTE is formated with one digit, so shows 1.2. Hope someone out there can help - I'm stumped. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMPRODUCT, 3 variables, 2 types of data in 1 column
You're welcome, Dana - thanks for feeding back.
Pete On Feb 7, 4:38*am, Dana M wrote: Thanks, Pete! *100% helpful! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct with 3 variables | Excel Discussion (Misc queries) | |||
Charts - Custom Types - Line Column - Data Series | Excel Discussion (Misc queries) | |||
Needed: Chart that combines clustered column and stacked column types | Charts and Charting in Excel | |||
Sun IF two data types are the same in a single column | Excel Discussion (Misc queries) | |||
Sort column containing different data types | Excel Discussion (Misc queries) |