Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct does not work after editing data
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct does not work after editing data
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. "Jackie K" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to keyboard navigate to other cells during editing formula, when arrow keys don't work | Excel Discussion (Misc queries) | |||
sumproduct doesn't work | Excel Worksheet Functions | |||
Sumproduct formulas not working after editing | Excel Worksheet Functions | |||
Sumproduct formulas not working after editing | Excel Worksheet Functions | |||
Will SUMPRODUCT work for this? | Excel Discussion (Misc queries) |