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
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
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies