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.

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.

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.

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.

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?