Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 146
  • Last Modified:

Excel 2013 Sum calls based on first two digits.

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
MarcusSjogren
Asked:
MarcusSjogren
  • 5
  • 4
1 Solution
 
ProfessorJimJamCommented:
see the attached example.
Book1.xlsb
0
 
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!
0
 
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ProfessorJimJamCommented:
please find attached.
Book1.xlsb
0
 
ProfessorJimJamCommented:
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
 
MarcusSjogrenAuthor Commented:
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
 
ProfessorJimJamCommented:
cell c11  shows the solution.

please see attached.
0
 
ProfessorJimJamCommented:
you are welcome.

glad it worked.
0
 
MarcusSjogrenAuthor Commented:
Very quick and correct answers!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now