Condensing several rows of data into a single row

[ATraveller]

Hello

As the title says, then I want to condense several rows of data into a single row.

The layout of my data can be seen in example1.

Any help would be greatly appreciated, thanks!

[Pete_UK]

Put this formula in I27:

=SUMPRODUCT((TEXT($B$4:$B$20,"mmyy")=TEXT(I$25,"mmyy"))*($C$4:$C$20=$H27),($D$4:$D$20+$E$4:$E$20))

then copy across and down. If you don't want the zero values to show, you can apply conditional formatting so that if the cell content is zero use a white font.

Hope this helps.

Pete

[Ace_XL]

In I27 use

=SUMPRODUCT($D$4:$D$20+$E$4:$E$20,--(DATE(YEAR($B$4:$B$20),MONTH($B$4:$B$20),1)=I$25),--($C$4:$C$20=$H27))

Copy across and down

[ATraveller]

Thank you both very much.

Pete, your formula seemed to have an error where it did not take years into account, so it would sum up the values for e.g. December 2011 and December 2010.

Ace, your formula worked perfectly.

Cheers!

[Pete_UK]

I've attached the file that I worked on - it seems okay to me.

Pete

[ATraveller]

Hmm, maybe it is just that one item in cell R27 then? Now that you mention it, the other numbers look fine.