Hello,
This is a bit complicated, hope there are 'Jedi' masters who can offer
solutions.

I am trying to extract data from a Table. This is a simple description of
table
- Top Row = Relevant header names + Header of dates 1, 2, 3...
- Column A = Specific times
- Column B = Fixed Cost $
- Column C to Z = Selected alphabets indicating item, Eg. b, c1, e, m,...

The result I am trying to achieve is...
- in a single horizontal row
- each cell C to Z sum's corresponding to the dates on Top Row
Eg. cell C100 is the sum of C2:C99, D100 = sum D2:D99
cell C101 is the sum of C2:C99, D101 = sum D2:D99...

The catch is this.
- each single row is a sum of selected alphabets / items
Eg. Row100 = sum 2:99 of all items with b
Row101 = sum 2:99 of all items with c1
Row102 = sum 2:99 of all items with e

And the data to sum is cost!
That means each time 'c1' appears in column C, C100 will add the cost.
Eg. 'c1' appears in D5, D10, D31; hence D101 will sum the cost in B5, B10,
B31.
And 'e' appears in D2, D12, D20; hence D102 will sum the cost in B2, B12,
B20.

Its kind of the opposite of formula VLookUp.
The summary extracts the data from a table and produce a summary table of
cost for each specific item.

By the way, I have tried Pivot Table, but did not turn out right because
1. There were a number of left columns
2. The outcome did not sum the cost of each item.
.... it was a mess.

Is it possible to work this out using plain Excel and not VB either.

If the description above is not clear, please email me at
.
Thank you in anticipation.

Hi Robin, sorry if I'm not understand exactly what are you looking for, but...

on C100 you can use =sumif(c$2:c$99,"b",$b$2:$b$99)
on c101 =sumif(c$2:c$99,"c1",$b$2:$b$99)
on c102 =sumif(c$2:c$99,"e",$b$2:$b$99)

copy it for the right column

hth
regards from Brazil
Marcelo



"Robin K." escreveu:

Hello,
This is a bit complicated, hope there are 'Jedi' masters who can offer
solutions.

I am trying to extract data from a Table. This is a simple description of
table
- Top Row = Relevant header names + Header of dates 1, 2, 3...
- Column A = Specific times
- Column B = Fixed Cost $
- Column C to Z = Selected alphabets indicating item, Eg. b, c1, e, m,...

The result I am trying to achieve is...
- in a single horizontal row
- each cell C to Z sum's corresponding to the dates on Top Row
Eg. cell C100 is the sum of C2:C99, D100 = sum D2:D99
cell C101 is the sum of C2:C99, D101 = sum D2:D99...

The catch is this.
- each single row is a sum of selected alphabets / items
Eg. Row100 = sum 2:99 of all items with b
Row101 = sum 2:99 of all items with c1
Row102 = sum 2:99 of all items with e

And the data to sum is cost!
That means each time 'c1' appears in column C, C100 will add the cost.
Eg. 'c1' appears in D5, D10, D31; hence D101 will sum the cost in B5, B10,
B31.
And 'e' appears in D2, D12, D20; hence D102 will sum the cost in B2, B12,
B20.

Its kind of the opposite of formula VLookUp.
The summary extracts the data from a table and produce a summary table of
cost for each specific item.

By the way, I have tried Pivot Table, but did not turn out right because
1. There were a number of left columns
2. The outcome did not sum the cost of each item.
... it was a mess.

Is it possible to work this out using plain Excel and not VB either.

If the description above is not clear, please email me at
.
Thank you in anticipation.

try something like
in A100 to Axxx enter the b,c1,e etc
in c100 enter
=sumif(c$2:c$99,$a100,$B$2:$B$99)

"Robin K." wrote:

Hello,
This is a bit complicated, hope there are 'Jedi' masters who can offer
solutions.

I am trying to extract data from a Table. This is a simple description of
table
- Top Row = Relevant header names + Header of dates 1, 2, 3...
- Column A = Specific times
- Column B = Fixed Cost $
- Column C to Z = Selected alphabets indicating item, Eg. b, c1, e, m,...

The result I am trying to achieve is...
- in a single horizontal row
- each cell C to Z sum's corresponding to the dates on Top Row
Eg. cell C100 is the sum of C2:C99, D100 = sum D2:D99
cell C101 is the sum of C2:C99, D101 = sum D2:D99...

The catch is this.
- each single row is a sum of selected alphabets / items
Eg. Row100 = sum 2:99 of all items with b
Row101 = sum 2:99 of all items with c1
Row102 = sum 2:99 of all items with e

And the data to sum is cost!
That means each time 'c1' appears in column C, C100 will add the cost.
Eg. 'c1' appears in D5, D10, D31; hence D101 will sum the cost in B5, B10,
B31.
And 'e' appears in D2, D12, D20; hence D102 will sum the cost in B2, B12,
B20.

Its kind of the opposite of formula VLookUp.
The summary extracts the data from a table and produce a summary table of
cost for each specific item.

By the way, I have tried Pivot Table, but did not turn out right because
1. There were a number of left columns
2. The outcome did not sum the cost of each item.
... it was a mess.

Is it possible to work this out using plain Excel and not VB either.

If the description above is not clear, please email me at
.
Thank you in anticipation.

Thank you Marcelo & bj.
....wonder why didn't think of this before :p

"bj" wrote:

try something like
in A100 to Axxx enter the b,c1,e etc
in c100 enter
=sumif(c$2:c$99,$a100,$B$2:$B$99)

"Robin K." wrote:

Hello,
This is a bit complicated, hope there are 'Jedi' masters who can offer
solutions.

I am trying to extract data from a Table. This is a simple description of
table
- Top Row = Relevant header names + Header of dates 1, 2, 3...
- Column A = Specific times
- Column B = Fixed Cost $
- Column C to Z = Selected alphabets indicating item, Eg. b, c1, e, m,...

The result I am trying to achieve is...
- in a single horizontal row
- each cell C to Z sum's corresponding to the dates on Top Row
Eg. cell C100 is the sum of C2:C99, D100 = sum D2:D99
cell C101 is the sum of C2:C99, D101 = sum D2:D99...

The catch is this.
- each single row is a sum of selected alphabets / items
Eg. Row100 = sum 2:99 of all items with b
Row101 = sum 2:99 of all items with c1
Row102 = sum 2:99 of all items with e

And the data to sum is cost!
That means each time 'c1' appears in column C, C100 will add the cost.
Eg. 'c1' appears in D5, D10, D31; hence D101 will sum the cost in B5, B10,
B31.
And 'e' appears in D2, D12, D20; hence D102 will sum the cost in B2, B12,
B20.

Its kind of the opposite of formula VLookUp.
The summary extracts the data from a table and produce a summary table of
cost for each specific item.

By the way, I have tried Pivot Table, but did not turn out right because
1. There were a number of left columns
2. The outcome did not sum the cost of each item.
... it was a mess.

Is it possible to work this out using plain Excel and not VB either.

If the description above is not clear, please email me at
.
Thank you in anticipation.