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
1
2	150101MABOCBOS	1		6
3	150201MABOCBOS	2
4	150301MABOCBOS	3
5	150401MABOCBOS	4
6	150501MABOCBOS	5
7	150601MABOCBOS	6
8	150701MABOCBOS	7
9	150801MABOCBOS	8

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet11​

Worksheet Formulas Cell Formula D2 =SUMPRODUCT(--(MID(A2:A9,3,2)>="01"),--(MID(A2:A9,3,2)<="03"),B2:B9) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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.