# SUM of filtered results

1. ## 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. ## 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. ## 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. ## 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. ## 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

6. ## 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. ## 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. ## Re: SUM of filtered results

Use AutoFilter or a PivotTable

9. ## 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

11. ## 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

13. ## 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. ## Re: SUM of filtered results

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

15. ## 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. ## 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. ## Re: SUM of filtered results

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

See file attached.

18. ## 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

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

#### 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