+ Reply to Thread
Results 1 to 18 of 18

SUM of filtered results

  1. #1
    Registered User
    Join Date
    02-03-2009
    Location
    Gosford, Australia
    MS-Off Ver
    Excel 2003
    Posts
    8

    SUM of filtered results

    Hi I am new to this forum and don't exactly know how to ask for this help. Here goes..

    I have a monthly work phone bill which I receive as an Excel document. I have to pay for personal calls and want to create a list of personal numbers which can be filtered and the costs summed. Prefereably an add in would be best. Possibly 2 icons, one for adding my personalised numbers (usually under 10 numbers). This could be saved to my home drive on the network so I can run the formula from any PC on the network.

    The other icon would be to run the report and do the calculations. So it only really looks in 2 column (mobile num, cost).

    Any help would be much appreciated.

    Bealy

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: SUM of filtered results

    Hi and welcome to the board.
    You could use a Pivot Table to get your results, so no need for add-ins.

    HTH

  3. #3
    Registered User
    Join Date
    02-03-2009
    Location
    Gosford, Australia
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: SUM of filtered results

    I want to make a tool that everyone at work can use (500 people). I can roll out an add in automatically so thats why I thought of it.

    Bealy

  4. #4
    Registered User
    Join Date
    11-19-2006
    Posts
    15

    Re: SUM of filtered results

    I don't understand the question, do you want to add your own list of numbers to the sheet first? do you have an example?

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: SUM of filtered results

    How do you intend differentiating between personal & business numbers, they will be different for each use. Any code would need to be able to do this. ApivotTable would be the simplest option
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  6. #6
    Forum Contributor
    Join Date
    10-11-2007
    Location
    Sweden
    MS-Off Ver
    365
    Posts
    251

    Re: SUM of filtered results

    I am not sure i understand this question but to sum a filtered column you need to use SUBTOTAL() function instead of sum(). Using sum() will sum all values, even those not filtered out.

  7. #7
    Registered User
    Join Date
    02-03-2009
    Location
    Gosford, Australia
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: SUM of filtered results

    I will try to explain it the best I can.

    I have a spreadsheet (which is a mobile phone bill) with 2 columns number dialed in column A, and cost in column B. I would like some numbers dialed (my home, wifes mobile etc) in column A to be highlighted and the associated costs in column B be subtotaled somewhere. The only things that are known each month when the bill arrives is my home number and my wifes mobile number. So these numbers could be entered at any stage, whether it be in a lookup file or manually typed somewhere each time.

    Hope this clears things up.

    Cheers

  8. #8
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: SUM of filtered results

    Use AutoFilter or a PivotTable

  9. #9
    Registered User
    Join Date
    02-03-2009
    Location
    Gosford, Australia
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: SUM of filtered results

    OK I set up a pivot table and pointed the bill to external data (private numbers on another Excel file). I can't seem to link the 2 bits of data together. Not sure what I am doing wrong. I resorted to not pointing to external data and just doing a find replace for my private numbers and replace them with colour. Then I can manually go through and add erverything up. This seems so manual and I am sure there is a way to automate it I just don't know the answer.

    Does anyone have a worksheet with a similar function that I can have a look at? That might help.

    Thanks

  10. #10
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: SUM of filtered results

    A view of your data would be helpful

  11. #11
    Registered User
    Join Date
    02-03-2009
    Location
    Gosford, Australia
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: SUM of filtered results

    I have attached a sample of the work phone bill I get. I have to pay for any personal calls. I have omitted several columns for simplicity. Below is also a sample of the attached file...

    Column A, Column B
    61403999888, 1.34
    1234567, .98
    9849383, .04
    9494837367, .13
    132244, .45
    266578899, 4.67
    61403999888, 2.88
    9849383, .09

    Lets say my 2 personal numbers which I call are 61403999888 and 9849383. As you can see there are 2 calls from each number and their associated call costs.

    I want to firstly work out the best way to input my personal numbers. I could do this by looking up another file, worksheet etc or i could manually enter the numbers each time I get the bill (preferably not).

    Secondly i want to identify the personal numbers and have the associated costs totalled in another cell. This will give me my total personal call costs. I have tried using vlookups however I am not very good with them and could only get them to return the first cost which matched the number l entered. See the following vlookup I tried

    Please Login or Register  to view this content.
    which only returned (1.34)

    Currently, everyone at work gets these mobile phone bills and has to manually go through and identify personal numbers and add the costs. Seems so backwards.

    Anyway if someone can help I will be so stoked.

    Bealy
    Attached Files Attached Files

  12. #12
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: SUM of filtered results

    I've added a PivotTable
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    02-03-2009
    Location
    Gosford, Australia
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: SUM of filtered results

    Thanks RoyUK. This is the result I want however is there a way to make it less manual (creating a pivot table and filtering the personal calls) as I get these bills every month and I already know my personal numbers which I call.

    If I could use a vlookup or some code it may be easier to achieve.

    Jamie

  14. #14
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: SUM of filtered results

    You could possibly use SUMIF, VLOOKUP won't produce the result you need.

  15. #15
    Registered User
    Join Date
    02-03-2009
    Location
    Gosford, Australia
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: SUM of filtered results

    Thanks. I have tested the SUMIF and it works. However is there a way to have the value in the middle of the SUMIF reference a file? Rather than just entering one phone number. See my example below:

    Please Login or Register  to view this content.
    Can that phonen number above be substituted with a bunch of numbers. I can achieve it with several SUMIFs with different numbers, see example below:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    This gives me 3 results which I can subtotal. I would rather it be easier to run, maybe an add-in or something with a macro and vb code behind it? I just don't know how to achieve it.

    Cheers

  16. #16
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: SUM of filtered results

    The most efficient way is the PivotTable. You could use a dynamic named range for the table. Create a template then simply paste the monthly data into the table & refresh the PivotTable. Or keep one master file to paste monthly data into & group the dates by months.

  17. #17
    Forum Contributor
    Join Date
    10-11-2007
    Location
    Sweden
    MS-Off Ver
    365
    Posts
    251

    Re: SUM of filtered results

    I created a sample workbook. I used sumproduct to sum the values.

    See file attached.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    02-03-2009
    Location
    Gosford, Australia
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: SUM of filtered results

    OK. I have come up with a form which does pretty much what I need. I have attached the spreadsheet so you can see it clearly. There are 2 things which I don't know how to fix.

    1. How can a make a button in Excel 2007 which sits in the QAT or menu. I don't want a button in a cell. Because i want this macro to be useable by others.

    2. When I click on "subtotal" button the screen flickers for several seconds while processing. I tried to disable screen refresh but that didn't seem to work.

    If anyone can help that would be great.

    Jamie
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1