Hi, I need some help with a formula. I have a spreadsheet that has all 12 months for the sheet tabs. They are named as follows:
Jan, Feb, Mar, Apr, May, Jun, etc...
I have clerks enter financial amounts into column A which is the Amount Due from the customer. Into column B I have them enter how many clearance items were sold. In column C is the amount of cash the customer gave the cashier and in column D is the change that the cashier needs to give the customer.
So one scenario would be:
Sheet Jan
A B C D
1. $100 1 $120 $20
2. $150 0 $160 $10
3. $345 3 $350 $5
So the first customer needs to pay $100 for 2 items but all I care about is how many of those items are clearance items. In this case, it was only 1 of the 2 items that were from the clearance section. The customer gave $120 and the cashier gives back $20 in change. For the second customer, he needs to pay $150 with no clearance items. The customer pays $160 and cashier gives $10 in change. The last customer needs to pay $345 and has bought 10 items but only 3 were clearance items. Customer pays $350 and cashier gives back $5 change.
At the end of the month sheets I have a "Totals" sheet. This is where I need help with a formula.
In cell B1 in the Totals sheet, I want it to sum up all the of column B in the Jan sheet. B2 would be the same for the Feb sheet and so forth for the rest of the months. Then in cell C1 I would want it to sum up the financial amounts in column C only if the cashier enters a 1 or greater in column B in the Jan sheet. The same goes for C2 for Feb and so forth.
Let me know if you need more details. Thank you so much for any help. I really appreciate it.
Jan, Feb, Mar, Apr, May, Jun, etc...
I have clerks enter financial amounts into column A which is the Amount Due from the customer. Into column B I have them enter how many clearance items were sold. In column C is the amount of cash the customer gave the cashier and in column D is the change that the cashier needs to give the customer.
So one scenario would be:
Sheet Jan
A B C D
1. $100 1 $120 $20
2. $150 0 $160 $10
3. $345 3 $350 $5
So the first customer needs to pay $100 for 2 items but all I care about is how many of those items are clearance items. In this case, it was only 1 of the 2 items that were from the clearance section. The customer gave $120 and the cashier gives back $20 in change. For the second customer, he needs to pay $150 with no clearance items. The customer pays $160 and cashier gives $10 in change. The last customer needs to pay $345 and has bought 10 items but only 3 were clearance items. Customer pays $350 and cashier gives back $5 change.
At the end of the month sheets I have a "Totals" sheet. This is where I need help with a formula.
In cell B1 in the Totals sheet, I want it to sum up all the of column B in the Jan sheet. B2 would be the same for the Feb sheet and so forth for the rest of the months. Then in cell C1 I would want it to sum up the financial amounts in column C only if the cashier enters a 1 or greater in column B in the Jan sheet. The same goes for C2 for Feb and so forth.
Let me know if you need more details. Thank you so much for any help. I really appreciate it.