Solved

Excel 2013 Sum calls based on first two digits.

Posted on 2014-10-02
9
136 Views
Last Modified: 2014-10-06
Hi,

I am currently trying to find the cost for telephone calls made from number 123-456789 to numbers starting with 7 and then sum the costs for all those calls. See example below.

The only thing is that it must not contain numbers that start with 77

I will then do similar matches with other numbers, say starting with the number 6.

Is this possible without very complicated macros?

From               To                  Cost
123-456789      71574588      0,2 - Included
123-456789      72574588      0,2 - Included
123-456789      73574588      0,2 - Included
123-456789      74574588      0,2 - Included
123-456789      75574588      0,2 - Included
123-456789      76574588      0,2 - Included
123-456789      77574588      0,2 - Not Included

SUM = 1,2.
0
Comment
Question by:MarcusSjogren
  • 5
  • 4
9 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40357472
see the attached example.
Book1.xlsb
0
 
LVL 4

Author Comment

by:MarcusSjogren
ID: 40364304
Sorry for my late reply but I haven't been able to test it yet. But it looks promising and I will revert tomorrow!
0
 
LVL 4

Author Comment

by:MarcusSjogren
ID: 40364773
HI again,

Sorry but it does not fully comply with what I was looking for. I wanted it to match on both source and destination number.
Now it is only looking for all "to-numbers" that doesn't start with 77.

Any ideas how to include the source-number matching as well? Added a new row to the bottom of the example table below.

From               To                  Cost
123-456789      71574588      0,2 - Included
123-456789      72574588      0,2 - Included
123-456789      73574588      0,2 - Included
123-456789      74574588      0,2 - Included
123-456789      75574588      0,2 - Included
123-456789      76574588      0,2 - Included
123-456789      77574588      0,2 - Not Included
223-456789      71574588      0,2 - Not Included
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40364787
please find attached.
Book1.xlsb
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 40364793
please bear in mind that my earlier formula was a correct solution to what you have asked initially.  what you have added  in your latest comment, is an additional condition which wasn't part of your initial question.

anyways, i have included now this new condition into the attachment just uploaded few seconds earlier.
0
 
LVL 4

Author Comment

by:MarcusSjogren
ID: 40364798
Hi,

Yes - I realize that I was too unclear with my request in my first comment, sorry about that.

Second one is working like a charm. Thanks a lot for your swift help!
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40364799
cell c11  shows the solution.

please see attached.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40364800
you are welcome.

glad it worked.
0
 
LVL 4

Author Closing Comment

by:MarcusSjogren
ID: 40364801
Very quick and correct answers!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Formula for excel 5 27
Password Protecting PDF's 8 44
Missing Categories and colors in Outlook & Outlook.com 3 71
EXCEL Addin problem 7 51
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
The viewer will learn how to edit text. This includes Font, Spacing, Resizing, Color, and other special text options.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now