Sumif/Sumproduct/Dsum ?

[Andrew Mackenzie]

Hi all,

This is my problem. I have a table of data something like this:

31-1-2008 28-2-2008 31-3-2008
London
London
New York
Paris
London
New York
New York
Milan

I want to create a summary table with totals like this:

28-2-2008
London
Paris
New York
Milan

I want the totals to change when I change the date above to reflect the
correct month totals. The dates above are all formatted as dates.

Any ideas much appreciated as always.

 

[Domenic]

Hi all,

This is my problem. I have a table of data something like this:

31-1-2008 28-2-2008 31-3-2008
London
London
New York
Paris
London
New York
New York
Milan

I want to create a summary table with totals like this:

28-2-2008
London
Paris
New York
Milan

I want the totals to change when I change the date above to reflect the
correct month totals. The dates above are all formatted as dates.

Any ideas much appreciated as always.

Assumptions:

1) For your source table, A2:A9 contains the city, B11 contains the
data, and B29 contains the data.

2) For your results table, F2:F5 contains the city, and G1 contains the
date.

Formula:

G2, copied down...

=SUMIF($A$2:$A$9,$F2,INDEX($B$2:$D$9,0,MATCH(G$1,$B$1:$D$1,0)))

 

[Andrew Mackenzie]

Hi Domenic,

Sheer bloody genius! Thanks a million for taking the time and trouble to
help me out!

Andrew

 

[Domenic]

Hi Domenic,

Sheer bloody genius! Thanks a million for taking the time and trouble to
help me out!

Andrew

You're very welcome! Thanks for the feedback!

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
No formulas needed.
http://www.mediafire.com/file/zyynndcdwzt/04_06_09.xlsx