Consolidating Data Based on Coding Structure

**addie** · 03-22-2009, 03:54 PM

How would I create a formula which consolidates response data based on the 6th character of the following series of codes?

Codes ------------------ Responses
R0801Z0.5-----------------400
R0801Z0.6-----------------500
R0801Z0.7-----------------600
R0802BZ.1-----------------220
R0802BZ.2-----------------506
R0802BZ.3-----------------200
R0603BA.3-----------------640
R0603BA.4-----------------820
R0603BA.5-----------------307
R0603BA.6-----------------912

**rylo** · 03-22-2009, 06:29 PM

Hi

Assuming your data is in the range A1:B11 then try

=SUMPRODUCT(--(MID(A2:A11,6,1)="Z"),(B2:B11))
and
=SUMPRODUCT(--(MID(A2:A11,6,1)="B"),(B2:B11))

rylo

**addie** · 03-22-2009, 10:22 PM

Thanks for providing a solution, rylo.

What if I wanted to pull data from another worksheet? Is there a way to combine the sumproduct formula with a vlookup formula?

Thanks,

Addie

**rylo** · 03-23-2009, 04:56 AM

Hi

Care to give some more detail on the setup? What are you trying to bring back with the vlookup?

Build a small example workbook with 2 sheets and explain what you want to do.

rylo

**royUK** · 03-23-2009, 06:22 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!