SUMIF based on a specific set of numbers in a text string

[christrudeaucpa]

Hello and thanks for your time.

I am attempting to create a SUMIF function that sums a set of values based on a specific spot in a text string.

Each value has a corresponding Item # made up of the following features:

Year Month Day State Program Code Location
15 01 01 MA BOC BOS

Here's the full text string in the cell for this example: 150101MABOCBOS

What I'm looking to do is Sum based on certain months, so in this case, I would need the SUMIF to find Item #s with 01, 02, 03 as a month. The month will always be located in the 3rd and 4th characters in the Item # text string.

Let me know if you'd like any further clarification. Any help would be appreciated.

Chris

 

[Eric W]

Maybe something like this?

Excel 2010

	A	B	C	D
150101MABOCBOS
150201MABOCBOS
150301MABOCBOS
150401MABOCBOS
150501MABOCBOS
150601MABOCBOS
150701MABOCBOS
150801MABOCBOS

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=SUMPRODUCT(--(MID(A2:A9,3,2)>="01"),--(MID(A2:A9,3,2)<="03"),B2:B9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[christrudeaucpa]

That worked great! Thank you very much Eric. You're a life (*read* time) saver!

I've always done strategic web searching for my Excel tips and tricks in the past, but this required some specific outreach. I'm happy to have had a successful attempt on this message board. Hopefully others can use this post for years to come!

 

[Eric W]

Glad to help! Thanks for the feedback.