Jul 15 2020 12:21 PM
Hi,
I am trying to figure out a formula to not count certain values if there's 0 in a cell
Basically if c3<0 then don't add the values from D3-D5-D8-D9
Makes sense? help????
Jul 15 2020 12:32 PM
I am not sure if this is what you are looking for ... if it is not please ignore it.
Freehand copied from the internet
If it is the solution for you then i would be happy to find out if I could help with a thumbs up :)
Nikolino
I know I don't know anything (Socrates)
Jul 15 2020 01:12 PM
I am trying to figure out a formula to not count certain values if there's 0 in a cell
Basically if c3<0 then don't add the values from D3-D5-D8-D9
You have said that you want to count values as well as don't add the values.
I'm not sure whether you want to count or sum, so here's both.
In the following formulas, the length of the array of 1/0 values is determined by the length of the data in column D (excluding the header) and the position of the 0s are determined by your request to exclude specifically D3, D5, D8 and D9.
As such, your exact implementation may differ.
SUM:
=SUMPRODUCT($D$2:$D$14,N(({1;0;1;0;1;1;0;0;1;1;1;1;1}+($C$3>=0))>0))
COUNT:
=SUMPRODUCT(N(({1;0;1;0;1;1;0;0;1;1;1;1;1}+($C$3>=0))>0))
As you can see, when C3 is less than 0, the sum of my sample data is 102 and the count is 9.
When C3 is 1, the sum is 158 and the count is 13.
Please include some sample data and scenarios with expected outcome in case this is not what you intended.
Please see attached and let me know if this helps.
Jul 15 2020 01:37 PM
Hi, I've added the sheet I'm working with if.
In the "add if a busser/runner wasn't working" that result should be if only 7% was taken rather than 14%.
Jul 15 2020 03:36 PM
Jul 15 2020 04:09 PM
I know, I prolly explained it horribly, sorry.
Bussers/runners/bartenders are an entity, so, they get 7%/7%/25% regardless of how many ppl are doing that job, 0-3.
Beth & Manny are runners that share 7% of the tip if they worked together, sometimes there's only 1 person working.
Bartender gets 25% after the 7%-7% has been taken out.
My problem is, what formula would it be if there was only 1 runner or busser working. What would be left after to take the 25%.
Jul 15 2020 04:28 PM
Solution
Ok, I understand now.
If you check the sum of hours worked for bussers and runners respectively, and only subtract when there are some hours worked by at least one busser or runner, then again only subtract from that when there are at least some hours worked by a bartender, you'll get what you need.
So, for cell H2 for Kristi, for example, you could use this:
=G2-IF(SUM($L$2:$L$3)>0,$N$2,0)-IF(SUM($L$6:$L$7)>0,$N$6,0)
That is to say, subtract from the total tips earned, the amount for the bussers only if bussers hours were greater than zero, and the amount for the runners only if the runners hours were greater than zero.
This amount is then what you calculate the bartender's tips from (which you already have in cells N10:P10).
Then finally, the amount left for the server is column H minus the bartender's tips but only if there were bartender hours worked. So this formula goes in B10:
=H2-IF(SUM($L$10:$L$11)>0,$N$10,0)
I edited a copy of your workbook on the sheet "New". See attached.
Jul 15 2020 04:48 PM
Thank you so much!!!! Problem solved!! Another satisfied customer. Appreciate the help.
Jul 15 2020 04:28 PM
Solution
Ok, I understand now.
If you check the sum of hours worked for bussers and runners respectively, and only subtract when there are some hours worked by at least one busser or runner, then again only subtract from that when there are at least some hours worked by a bartender, you'll get what you need.
So, for cell H2 for Kristi, for example, you could use this:
=G2-IF(SUM($L$2:$L$3)>0,$N$2,0)-IF(SUM($L$6:$L$7)>0,$N$6,0)
That is to say, subtract from the total tips earned, the amount for the bussers only if bussers hours were greater than zero, and the amount for the runners only if the runners hours were greater than zero.
This amount is then what you calculate the bartender's tips from (which you already have in cells N10:P10).
Then finally, the amount left for the server is column H minus the bartender's tips but only if there were bartender hours worked. So this formula goes in B10:
=H2-IF(SUM($L$10:$L$11)>0,$N$10,0)
I edited a copy of your workbook on the sheet "New". See attached.