Sumproduct does not work after editing data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jackie K - 30 Mar 2007 21:38 GMT

Shift    Status        1st Shift     20
2    2        2nd Shift 3
1    2        3rd Shift 3
2    1        Unknown    0
2    1        Total Active    26

Above is the spreadsheet that I am working with. I am using a SUMPRODUCT
formula to tell me how many employees are on each shift. Below are the
formulas I am using to get the results listed after 1st Shift, 2nd Shift and
3rd Shift. The formula works only if the data is not edited. If I change
any number in the shift column, the formulas do not seem to calculate the
value. Any ideas? Does this formula only work on unideted data?

{=SUMPRODUCT(--(L1:L5600="1"),--(M1:M5600=1))}

{=SUMPRODUCT(--(L1:L5600="2"),--(M1:M5600=1))}

{=SUMPRODUCT(--(L1:L5600="3"),--(M1:M5600=1))}

Any help would be appreciated.

Jackie K

Reply to this Message

Bob Umlas - 30 Mar 2007 21:54 GMT

Looks OK EXCEPT you seem to be array-entering them. SUMPRODUCT doesn't
require array-entry. You didn't say what's not working -- wrong answers?
#VALUE? same answers in each formula? what's happening?
Bob Umlas
Excel MVP

> Shift Status 1st Shift 20
> 2 2 2nd Shift 3
[quoted text clipped - 19 lines]
>
> Jackie K

Reply to this Message

Toppers - 30 Mar 2007 21:56 GMT

Which column is the employee data in ... as it only checks to values of 1,2
or 3 in L (Shifts?) and 1 in M? Does M=1 indicate employee is present?

Changing values in L & M worked OK for me and there is nothing wrong with
formulae if above applies.

And you don't need to enter with CtrL+SHift+Enter as your posting implies
from the {} brackets.

> Shift Status 1st Shift 20
> 2 2 2nd Shift 3
[quoted text clipped - 18 lines]
>
> Jackie K

Reply to this Message