Results 1 to 8 of 8

Thread: Need Help Creating A Formula

  1. #1

    Need Help Creating A Formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.



    Click image for larger version. 

Name:	excelreport.jpg 
Views:	18 
Size:	100.5 KB 
ID:	6331

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    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
    Last edited by Hercules1946; 2017-01-27 at 05:40 PM.

  3. #3
    Quote Originally Posted by Hercules1946 View Post
    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. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Are you looking to select the customer you want, or do them all?

  5. #5
    Quote Originally Posted by Hercules1946 View Post
    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. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    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
    Attached Files Attached Files

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,621
    Articles
    0
    Excel Version
    365
    …or a pivot table:
    Click image for larger version. 

Name:	Capture3.JPG 
Views:	5 
Size:	21.7 KB 
ID:	6336
    or same pivot differently displayed:
    Click image for larger version. 

Name:	Capture3.JPG 
Views:	5 
Size:	16.6 KB 
ID:	6337
    in attached.
    Attached Files Attached Files

  8. #8
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by p45cal View Post
    …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
  •