Data, lookups, and sums

[FJDx]

I have an Excel spreadsheet as follows:

Column A Dates in dd/mm/yy format
Column B Additional data (text)
Column C Group of figures (a)
Column D Additional data (text)
Column E Group of figures (b)

In another spreadsheet I have:

Row 1 Months going horizontally in mmm-yy format from Feb-04 to Jan-05
Row 2 Sum of figures

I would like the sum of figures to be calculated as Group of figures (a)
and (b) depending on what the corresponding date is.

I thought this would be similar to a lookup table. However, I need to be
able to add the sum of the two figures, and I need to look up the date
based on a month format, rather than individual days.

 

[Ken Wright]

Have you considered a Pivot Table?

Make sure your data has headers, and then put in a calculated field adding
together the two number group fields then use that in the DATA field of the
Pivot table. With dates across the top in the COLUMN fields you can simply
group on years and months to give you what you want.

 

[olark]

You should be able to use the conditional sun wizard to help make the
fomulas. If you don't have it installed got the add-ins and install
it. It's a very handy wizard.

Ian

 

[FJDx]

Have you considered a Pivot Table?

Make sure your data has headers, and then put in a calculated field adding
together the two number group fields then use that in the DATA field of the
Pivot table. With dates across the top in the COLUMN fields you can simply
group on years and months to give you what you want.

Thanks but I had to do it for a number of columns. I managed to create this
array which works:
=SUM((MONTH(X!$B$4:$B$368)=MONTH(B$1))*X!$L$4:$L$368)+SUM((MONTH(X!$B$4:$B$
368)=MONTH(B$1))*X!$O$4:$O$368)

(where B4 was the month along the top)