Excel 2013 Sum calls based on first two digits.


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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
see the attached example.
MarcusSjogrenAuthor Commented:
Sorry for my late reply but I haven't been able to test it yet. But it looks promising and I will revert tomorrow!
MarcusSjogrenAuthor Commented:
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
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

ProfessorJimJamMicrosoft Excel ExpertCommented:
please find attached.
ProfessorJimJamMicrosoft Excel ExpertCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MarcusSjogrenAuthor Commented:

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!
ProfessorJimJamMicrosoft Excel ExpertCommented:
cell c11  shows the solution.

please see attached.
ProfessorJimJamMicrosoft Excel ExpertCommented:
you are welcome.

glad it worked.
MarcusSjogrenAuthor Commented:
Very quick and correct answers!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.