Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif and wildcards
I column B I have code numbers, 4 and 5 digit. In column E I have
corresponding financial values. I want to sum values which correspond to sections of code numbers. ie by reading the first two or three digits of grouped code number. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif and wildcards
Perhaps something like this:
3 digit =SUMPRODUCT((LEFT(B2:B100,3)="111")*E2:E100) 2 digit =SUMPRODUCT((LEFT(B2:B100,2)="11")*E2:E100) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "oscarcounts" wrote: I column B I have code numbers, 4 and 5 digit. In column E I have corresponding financial values. I want to sum values which correspond to sections of code numbers. ie by reading the first two or three digits of grouped code number. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif and wildcards
=SUMIF(B2:B100,"111*",E2:E100)
and so on -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "oscarcounts" wrote in message ... I column B I have code numbers, 4 and 5 digit. In column E I have corresponding financial values. I want to sum values which correspond to sections of code numbers. ie by reading the first two or three digits of grouped code number. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif and wildcards
Thank you for this formula. It works if I modify the array in line with the
codes. The difficulty I have is that in some cases the first three digits of the 4 and 5 digit codes are the same. Is there a remedy? "Max" wrote: Perhaps something like this: 3 digit =SUMPRODUCT((LEFT(B2:B100,3)="111")*E2:E100) 2 digit =SUMPRODUCT((LEFT(B2:B100,2)="11")*E2:E100) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "oscarcounts" wrote: I column B I have code numbers, 4 and 5 digit. In column E I have corresponding financial values. I want to sum values which correspond to sections of code numbers. ie by reading the first two or three digits of grouped code number. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif and wildcards
Maybe, using MID instead of LEFT in the earlier expression
Post some examples of your data in col B -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "oscarcounts" wrote in message ... Thank you for this formula. It works if I modify the array in line with the codes. The difficulty I have is that in some cases the first three digits of the 4 and 5 digit codes are the same. Is there a remedy? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wildcards and if | Excel Worksheet Functions | |||
Wildcards in SUMIF Function | Excel Worksheet Functions | |||
Wildcards in RTD | Excel Discussion (Misc queries) | |||
Wildcards | Excel Worksheet Functions | |||
Problems with SUMIF function and Wildcards (* and ?) | Excel Worksheet Functions |