Multiple look-up / summing

[MikeCM]

I am getting stuck on a lookup / counting formula, and would appreciate
advice.

In one sheet I have a table where column E contains certain key
reference values, and which in columns F onwards contain data for
various months with row 1 containing the month headers (i.e. cell F1
contains Nov-06, G1 contains Dec-06, and so on through to Dec-08).
Example table as follows:

E F G

1 Company Nov-06 Dec-06
2 Firm A 13 7
3 Firm B 27 34


In another worksheet, I have a table, which I have named source_data
let's say, which contains numeric data. In this table, the first
column contains the same key reference values (multiple instances of
the same reference values) and beginning in the 5th column is the data
which to count. The cell in row 1 of the 5th column is the start of the
month headers (i.e. R1C5 is source_data is Nov-06, R1C6 is Dec-06, and
so on). The number of months and hence the number of columns in
source_data is not fixed and will vary (the name adapts to however many
columns there are) but would not go beyond Dec-08.

Column1 Column2 Column3 Column4 Column5 Column6

1 Company misc misc misc Nov-06 Dec-06
2 Firm B misc misc misc 27 34
3 Firm A misc misc misc 6 3
4 Firm C misc misc misc 10 6
5 Firm A misc misc misc 7 4

What I am trying to do is write into the first table a formula that
looks into the second table source_data and up and across both for each
month and all the entries for a particular firm and adds them all
together into the first table. Is there an easy way of doing this
lookup and summing? I have not had much success but intuitively it
seems straightforward!

 

[Franz Verga]

I am getting stuck on a lookup / counting formula, and would
appreciate advice.

In one sheet I have a table where column E contains certain key
reference values, and which in columns F onwards contain data for
various months with row 1 containing the month headers (i.e. cell F1
contains Nov-06, G1 contains Dec-06, and so on through to Dec-08).
Example table as follows:

E F G

1 Company Nov-06 Dec-06
2 Firm A 13 7
3 Firm B 27 34


In another worksheet, I have a table, which I have named source_data
let's say, which contains numeric data. In this table, the first
column contains the same key reference values (multiple instances of
the same reference values) and beginning in the 5th column is the data
which to count. The cell in row 1 of the 5th column is the start of
the month headers (i.e. R1C5 is source_data is Nov-06, R1C6 is
Dec-06, and so on). The number of months and hence the number of
columns in source_data is not fixed and will vary (the name adapts to
however many columns there are) but would not go beyond Dec-08.

Column1 Column2 Column3 Column4 Column5 Column6

1 Company misc misc misc Nov-06 Dec-06
2 Firm B misc misc misc 27 34
3 Firm A misc misc misc 6 3
4 Firm C misc misc misc 10 6
5 Firm A misc misc misc 7 4

What I am trying to do is write into the first table a formula that
looks into the second table source_data and up and across both for
each month and all the entries for a particular firm and adds them all
together into the first table. Is there an easy way of doing this
lookup and summing? I have not had much success but intuitively it
seems straightforward!

I think you could use a formula with VLOOKUP and MATCH functions or also a
SUMPRODUCT. It dependes if you need only to search and copy data or also to
sum them.

You could upload an example file with some data to www.rapidshare.de


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[MikeCM]

OK - thanks for that, this is useful. I am still figuring it out!