Sumproduct does not work after editing data

[Jackie K]

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

 

[Bob Umlas]

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

 

[Toppers]

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.