SOLVED

if/or/and fomulas

%3CLINGO-SUB%20id%3D%22lingo-sub-2429014%22%20slang%3D%22en-US%22%3Eif%2For%2Fand%20fomulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2429014%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20create%20a%20spreadsheet%20for%20tipping%20support%20staff%20in%20restaurant%20but%20the%20percentage%20changes%20if%20there%20is%20more%20than%20one%20employee%20on%20during%20a%20shift.%20How%20do%20I%20write%20a%20formula%20that%20says%20if%20more%20than%20one%20cell%20containing%20names%20is%20filled%20out%2C%20they%20get%206.5%25%2C%20but%20if%20only%20one%20cell%20is%20filled%20out%2C%20they%20get%205%25%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2429014%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2429069%22%20slang%3D%22en-US%22%3ERe%3A%20if%2For%2Fand%20fomulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2429069%22%20slang%3D%22en-US%22%3EAssuming%20A2%3AA20%20is%20the%20name%20containing%20range%2C%20I%20would%20do%20something%20like%20%3D%20IF%20(%20COUNTA%20(A2%3AA20)%20%3D%201%2C%206.5%25%2C%205%25)%3CBR%20%2F%3E%3CBR%20%2F%3Ecounta%20just%20counts%20the%20number%20of%20employees%2C%20then%20you%20can%20modify%20what%20happens%20if%20certain%20criteria%20are%20met.%20like%20multiply%20Sales%20in%20B%3AB%20*%206.5%25%20like%2C%20%3D%20IF%20(%20COUNTA%20(A2%3AA20)%20%3D%201%2C%20B2%20*%206.5%25%2C%20B2%20*%205%25)%3C%2FLINGO-BODY%3E
New Contributor

I'm trying to create a spreadsheet for tipping support staff in restaurant but the percentage changes if there is more than one employee on during a shift. How do I write a formula that says if more than one cell containing names is filled out, they get 6.5%, but if only one cell is filled out, they get 5%?

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution
Assuming A2:A20 is the name containing range, I would do something like = IF ( COUNTA (A2:A20) = 1, 6.5%, 5%)

counta just counts the number of employees, then you can modify what happens if certain criteria are met. like multiply Sales in B:B * 6.5% like, = IF ( COUNTA (A2:A20) = 1, B2 * 6.5%, B2 * 5%)

@DKoontz needed a slight tweak, used >1 rather than the =1, but I had no idea about the COUNTA prompt, so thank you very much, can't even describe the issues I've had trying to set this up but it's all done now