# Thread: Need Help Creating A Formula

1. ## Need Help Creating A Formula

So i have a report that i run on a weekly bases that has a list of all my customers and how many of our delivery bin the drivers have left and picked up from there location. What i'm trying to do is find a way to add up all the boxes in and boxes out for the customers and be able to see which of my customers are not returning the boxes. The current report i get is from my Fleet Management app which just gives me a report based on what the drivers input on the mobile app but when it exports the report i will have an 2 rows for a customer one thats boxes in and the boxes out. i added an example picture as i know im very bad at explaining. Any help would be greatly appreciated.

2. Try:
Code:
``` =SUMIF(\$F\$2:\$F\$17,"Boxes In",\$G\$2:\$G\$17)

And:

=SUMIF(\$F\$2:\$F\$17,"Boxes Out",\$G\$2:\$G\$17)

```
For the customer shortfalls, in col H, subtract Boxes Out from Boxes In for each account

3. Originally Posted by Hercules1946
Try:
Code:
``` =SUMIF(\$F\$2:\$F\$17,"Boxes In",\$G\$2:\$G\$17)

And:

=SUMIF(\$F\$2:\$F\$17,"Boxes Out",\$G\$2:\$G\$17)

```
For the customer shortfalls, in col H, subtract Boxes Out from Boxes In for each account
First off thanks for the reply.
That's the way i currently do it but i'm trying to find a way to have it add up the totals per customer as the sheet usually has over 5000 rows and customer entries all over the place like you might see an entry for customer "A" on rows 1 and 2 and again on rows 120 and 121 and so on which makes it hard having to find all the entries and go through adding them up.

4. Are you looking to select the customer you want, or do them all?

5. Originally Posted by Hercules1946
Are you looking to select the customer you want, or do them all?
I would like it to add up up for all customers but keep the totals separated.

6. In which case we switch the SUMIF calculation to test col E instead of Col F. Im assuming both box counts are positive, so we also need to turn Boxes In negative in order to get the shortfall caculation right. Im doing this in helper column H. We also need a unique list of Account names that Ive generated in Col K using the Advanced Filter which conveniently lets you put the reduced list in a separate column if you don't want to over-write the source list.
See Attached

7. …or a pivot table:

or same pivot differently displayed:

in attached.

8. Originally Posted by p45cal
…or a pivot table:
If Im doing anything apart from the most basic pivot table, I just never seem to be able to get it to do what I want, so its a last resort

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•