Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-759783%22%20slang%3D%22en-US%22%3EFormula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759783%22%20slang%3D%22en-US%22%3EHi%20there%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20someone%20that%20can%20help%20me%20with%20a%20rota%20spreadsheet%20and%20calculating%20the%20number%20of%20hours%20a%20day%20(over%20a%20month)%20please%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20formula%3CBR%20%2F%3E%3CBR%20%2F%3E%3DSUMPRODUCT((%24B7%3A%24B35%3DShiftNames)*(Shifts!%24D%243%3A%24D%2427))%3CBR%20%2F%3E%3CBR%20%2F%3Ebut%20it's%20faulting%20somewhere%20in%20the%20formula%2C%20I%20can't%20figure%20it%20out%3CBR%20%2F%3E%3CBR%20%2F%3EI%20can%20send%20a%20screenshot%20of%20what%20the%20rota%20looks%20like%20or%20if%20easier%20I%20can%20send%20over%20a%20copy%20of%20the%20rota%3F%3F%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20in%20advance%3CBR%20%2F%3E%3CBR%20%2F%3ENatasha%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-759783%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eformula%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-759813%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759813%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F378027%22%20target%3D%22_blank%22%3E%40NMGIBBS1976%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3Ewould%20you%20please%20share%20sample%20data%20in%20Excel%3F%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-760027%22%20slang%3D%22en-US%22%3ERE%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-760027%22%20slang%3D%22en-US%22%3EHi%20Natasha%2C%20SUMPRODUCT%20requires%20vectors%20(a%20column%20or%20a%20row%20of%20data)%20to%20be%20of%20equal%20size.%20B7%3AB35%20has%2029%20cells%3B%20D3%3AD27%20has%2025%20cells.%20It%20will%20spit%20the%20dummy.%20Please%20make%20your%20column%20references%20of%20equal%20length.%3C%2FLINGO-BODY%3E
Highlighted
NMGIBBS1976
Occasional Visitor
Hi there

Is there someone that can help me with a rota spreadsheet and calculating the number of hours a day (over a month) please?

I have a formula

=SUMPRODUCT(($B7:$B35=ShiftNames)*(Shifts!$D$3:$D$27))

but it's faulting somewhere in the formula, I can't figure it out

I can send a screenshot of what the rota looks like or if easier I can send over a copy of the rota???

Thanks in advance

Natasha
2 Replies

@NMGIBBS1976 

Hi

would you please share sample data in Excel?

Nabil Mourad

Hi Natasha, SUMPRODUCT requires vectors (a column or a row of data) to be of equal size. B7:B35 has 29 cells; D3:D27 has 25 cells. It will spit the dummy. Please make your column references of equal length.
Related Conversations