Count If Formula

[data808]

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.

 

[miss_ell]

Hello,

Hopefully this should give you a start:

B1 =SUM(Sheet1!B:B)
C1 =SUMIF(Sheet1!B:B,">0",Sheet1!C:C)

 

[Bairn90]

In your totals sheet I presume you have the months in collumn A?

If so then in B1 on your Totals sheet you would put "=SUM(INDIRECT("'"&A1&"'!B:B"))". This will link to the Jan tab if the text "Jan" is in cell A1. Assuming you then have Feb, Mar etc in the cells below you can then drag this formula down and it will sum everything in the tabs in column B.

In C1 the formula you want is "=SUMIF(INDIRECT("'"&A1&"'!B:B"),">0",INDIRECT("'"&A1&"'!C:C"))". This works the same way as the formula above, drag it down and it will sum up the amounts in column C for that sheet where the entry in B is not 0.

I think that should give you all you want. If not let me know.

 

[data808]

thank you so much. this worked like a charm.

 

[data808]

thank you very much for your reply. i tried the formula from the poster above you and it worked. I will keep your formula for reference in the future. thanks again for all your help.