Help with Monthly Consolidation by Category

[Guest]

Hi all,

I have a MTD consolidation (average) by category problem that I desparately
need your help. Here is what my data source may look like:

DATE NAME SCORE
1/1/2006 John 8.70
1/11/2006 Donna 7.80
1/21/2006 Lucy 9.10
1/25/2006 Donna 8.60
1/31/2006 John 9.90
2/3/2006 John 8.80
2/10/2006 Lucy 9.50
2/20/2006 Donna 9.80
3/2/2006 Jane 9.00
3/7/2006 John 9.50
3/12/2006 Donna 9.20
3/22/2006 Lucy 8.90
3/30/2006 Donna 9.00

Here is what my report would look like:

MONTH NAME MTD AVE YTD AVE
JAN Donna 8.20 8.20
JAN John 9.30 9.30
JAN Lucy 9.10 9.10
FEB Donna 9.80 9.30
FEB John 8.80 9.13
FEB Lucy 9.50 8.73
MAR Donna 9.10 9.17
MAR Jane 9.00 9.23
MAR John 9.50 8.88
MAR Lucy 8.90 9.00

Since the date range varies for each month, how would you define your
monthly date range in order to make the consolidation by category and by
month easier and dynamic?

Thanks so much!

Sally

 

[Carim]

Sally,

Why not using a pivot table ...???

Cheers
Carim

 

[Guest]

For January MTD Ave:


=SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14="John"),$C$2:$C$14)/SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14="John"))

Or

=SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14=F3),$C$2:$C$14)/SUMPRODUCT(--(MONTH($A$2:$A$14)=1),--($B$2:$B$14=F3))

HTH

 

[Guest]

..... should have been ...

=SUMPRODUCT(--(TEXT($A$2:$A$14,"mmm")="Jan"),--($B$2:$B$14="John"),$C$2:$C$14)/SUMPRODUCT(--(TEXT($A$2:$A$14,"mmm")="Jan"),--($B$2:$B$14="John"))

 

[Guest]

Thank you Carim and Toppers. The pivot table solution is the most
straightforward, thanks! But I am also interested in Topper's array
solution. How would you apply that to varying ranges? My date ranges change
everyday -- everyday new scores populate so at the end of the day I don't
know what ranges those dates will fall in.

You guys are great, thanks again!

Sally

 

[Guest]

I'm not sure what you mean by varying ranges: just ensure the highest range
index is greater than your expected maximum. Example below assumes no more
than 1000 rows.

=SUMPRODUCT(--(TEXT($A$2:$A$1000,"mmm")="Jan"),--($B$2:$B$1000="John"),$C$2:$C$14)/SUMPRODUCT(--(TEXT($A$2:$A$1000,"mmm")="Jan"),--($B$2:$B$1000="John"))

You can replace literals with cells e.g. X1="Jan", X2="John"

=SUMPRODUCT(--(TEXT($A$2:$A$1000,"mmm")=X1),--($B$2:$B$1000=X2),$C$2:$C$14)/SUMPRODUCT(--(TEXT($A$2:$A$1000,"mmm")=X1),--($B$2:$B$1000=X2))