Sumif/Sumproduct/Dsum ?
 

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.

Sumif/Sumproduct/Dsum ?
 

In article ,
"Andrew Mackenzie" wrote:

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, B1:D1 contains the
data, and B2:D9 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)))

--
Domenic
http://www.xl-central.com

Sumif/Sumproduct/Dsum ?
 

Hi Domenic,

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

Andrew
"Domenic" wrote in message
...
In article ,
"Andrew Mackenzie" wrote:

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, B1:D1 contains the
data, and B2:D9 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)))

--
Domenic
http://www.xl-central.com

Sumif/Sumproduct/Dsum ?
 

In article ,
"Andrew Mackenzie" wrote:

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!

--
Domenic
http://www.xl-central.com