WildCards in Sumif Function

[Hardeep Kanwar]

Hi! Experts

I am trying to sum the Cities total if they Match.

See the Example


Table1 SumIf Expected Result
Bangalore 309 1682 1682
Chennai 84 946 862
Hubli 15 149 149
Hyderabad 144 1438 1294
Shimla 1 1 1
Vijaywada 8 26 26
Vishakhapatnam 19 19 91
Tirupathi 20 21 29


Table2 Total
Aurangabad 2
Bangalore 1373
Chandigarh 1
Chennai 862
Hubli 134
Hyderabad 1294
Kerala 1
Kolkatta 1
Madurai 2
Pune 1
Tirupathi 1
Tirupati 8
Trivandrum 2
Vijaywada 18
Vishakapatnam 72

Is it Possible to Use WildCards in Sumif Function.

Like in My Example

See in First Table in E20 Tirupathi

And in Second Table in J12 and J13 Tirupathi and Tirupati ,Respectively

They are Same now i want to get Total in Table 1

The Total Should Be 29

Right now i am using This

=SUMIF($A$17:$A$31,A4,$B$17:$B$31)+B4

Expected Result Should be


Bangalore 1682
Chennai 862
Hubli 149
Hyderabad 1294
Shimla 1
Vijaywada 26
Vishakhapatnam 91
Tirupathi 29


Thanks in Advance

Hardeep kanwar

 

[Jacob Skaria]

If you want to go with the first n character match then try the below
formula...

'for first 5 character match
=SUMPRODUCT(--(LEFT($A$17:$A$31,5)=LEFT(A4,5)),$B$17:$B$31)

If this post helps click Yes

 

[Bob Phillips]

You can have wildcards in SUMIF

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