Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
WildCards in Sumif Function
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
WildCards in Sumif Function
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 --------------- Jacob Skaria "Hardeep Kanwar" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
WildCards in Sumif Function
You can have wildcards in SUMIF
=SUMIF(A:A,"RO*",B:B) -- __________________________________ HTH Bob "Hardeep Kanwar" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If function with text & wildcards | Excel Worksheet Functions | |||
sumif and wildcards | Excel Worksheet Functions | |||
IF function and wildcards | Excel Worksheet Functions | |||
Wildcards in SUMIF Function | Excel Worksheet Functions | |||
Problems with SUMIF function and Wildcards (* and ?) | Excel Worksheet Functions |