Using SUMPRODUCT, 3 variables, 2 types of data in 1 column

D

Dana M

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!
 
P

Pete_UK

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
 
D

Dana M

Thanks, Pete! 100% helpful!

Pete_UK said:
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
Click to expand...
 
Top