SUMIF with phone's area code and wildcard * being the criteria

0
votes

Trying to perform SUMIF on a range of rows in a column if they meet the criteria. For example, I want to sum up the rate for all rows that contain area code 212 in column A.

I tried to the following formula at the start:

=SUMIF(A:A,"212*",B:B)

Also tried to set column A to text/general/number formats. Tried using the &("212"&"" and 212&""). None of those SUMed values up.

      A          B
+------------+-------+
| Call       | Rate  |
+------------+-------+
| 2125689034 | 0.050 |
| 3478568843 | 0.045 |
| 7182511265 | 0.047 |
| 2128603619 | 0.050 |
| 7180530692 | 0.046 |
+------------+-------+
1
excelexcel-formula
Look into using pivot tables and watch your jaw drop when you see how easy and powerful they are. – Tim Biegeleisen
With your formula, go into your A cells, and hit F2 then Enter. I did that and the SumIf() correctly output the answer. I don't know why it didn't do that before "re-entering the cell". I suspect it's the cell type or how Excel's reading them. – BruceWayne
The problem is that the phone numbers are numbers and not text so you'll have to treat them as numbers in your formula. If you must use SUMIF: =SUMIF(A:A,">=2120000000",B:B)-SUMIF(A:A,">=2130000000",B:B) and if you have Excel 2007 or higher you can use SUMIFS: =SUMIFS(B:B,A:A,">=2120000000",A:A,"<2130000000") – tigeravatar

1 Answers

2
votes

You can use SUMPRODUCT:

=SUMPRODUCT((LEFT($A$2:$A$6,3)="212")*B2:B6)

![enter image description here