Totalising different payment types

[Marvick1]

Hi,

I've already posted this as a reply to an older post but I'm not sure if it's been swallowed up by the board??

"Hi, everyone,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I've failed miserably with my task!! To recap, here's what I need to do:<o></o>
<o></o>
* I pay compensation to customers after they've travelled on holiday - sometimes it's ex-gratia (i.e. my company pays the compensation), other times it's a refund (from our suppliers), some amounts can be reclaimed, others can't.<o></o>
* I need to show, each month, and by the date of travel, how much has been paid & whether it's ex-gratia or a refund.

Here's my template that, on one tab shows how much has been paid:<o></o>

<TABLE style="WIDTH: 360pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=479 border=0 x:str><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 5120" width=140><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 72pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=96 height=17>Country
</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Ref
</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=77>DOD
</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 77pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=102>Value of Cheque
</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 105pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=140>Ex-gratia? Refund?
</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 72pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=96 height=34>Austria AUS
</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 48pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num>503506
</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 58pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=77 x:num="40894">17-Dec-2011
</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 77pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=102 x:num="25">£25.00
</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 105pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=140>Ex-gratia, NON reclaimable
</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 72pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=96 height=34>France FRA
</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 48pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num>507294
</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 58pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=77 x:num="40894">17-Dec-2011
</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 77pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=102 x:num="150">£150.00
</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 105pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=140>Refund, reclaimable
</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 72pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=96 height=34>Austria AUS
</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 48pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num>503229
</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 58pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=77 x:num="40894">17-Dec-2011
</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 77pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=102 x:num="25">£25.00
</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 105pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=140>Ex-gratia, reclaimable
</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 72pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=96 height=34>Andorra AND
</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 48pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num>503999
</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 58pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=77 x:num="40894">17-Dec-2011
</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 77pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=102 x:num="25">£25.00
</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 105pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=140>Ex-gratia, NON reclaimable
</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 72pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: transparent" width=96 height=35>Austria AUS
</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 48pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 x:num>505431
</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 58pt; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=77 x:num="40943">04-Feb-2012
</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="100">£100.00
</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Refund, NON reclaimable

</TD></TR></TBODY></TABLE>​

: and my summary, on a second tab, in this format, needs to show:
<TABLE style="WIDTH: 287pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=382 border=0 x:str><COLGROUP><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5339" width=146><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><TBODY><TR style="HEIGHT: 18.75pt" height=25><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 108pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18.75pt; BACKGROUND-COLOR: silver" width=144 height=25></TD><TD class=xl35 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 179pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=238 colSpan=2 x:num="40878">December 2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD class=xl31 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" colSpan=2>UK PAYMENTS</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD class=xl33 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" colSpan=2>CHEQUE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Ex-gratia</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Refund</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Andorra AND</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="25">£25.00</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0">£0.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Austria AUS</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="50">£50.00</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0">£0.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Bulgaria BUL</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0">£0.00</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0">£0.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Canada CAN</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0">£0.00</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0">£0.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>France FRA</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0">£0.00</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="150">£150.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Italy ITA</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0">£0.00</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0">£0.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Slovenia SLO</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0">£0.00</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0">£0.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Spain SPA</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0">£0.00</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0">£0.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Switzerland SWI</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0">£0.00</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0">£0.00</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>USA USA</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0">£0.00</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0">£0.00</TD></TR><TR style="HEIGHT: 18pt" height=24><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>OVERALL</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="75" x:fmla="=SUM(B5:B14)">£75.00</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="150" x:fmla="=SUM(C5:C14)">£150.00</TD></TR></TBODY></TABLE>

I was fine when I only had to show how much had been paid, by date of travel, by country using =SUMIF & a LOOKUP but depsite my best efforts, I cannot adapt my original formulae to this new task of breaking down the amounts paid further. My reputation as a 'miracle worker' seems to be in jeopardy!!

As always, any help/advice would be greatly appreciated!!!

Kind regards,
Martin......."

 

[GlennUK]

Have you tried a PivotTable?

 

[Marvick1]

Hi, Glenn,

I though to of that but I'm trying to avoid it, solely becuase I had it working previously with a marvellous formulae that did the job perfectly:

=SUMIF('CHEQUES MASTER'!$N:$N,$A6&":"&LOOKUP(2,1/($B$2:$B$5=$B$2),$B$2:$B$2),'CHEQUES MASTER'!$E:$E)

But despite my best efforts, I just can't adapt it to cater for the new requirements.

Cheers,
Martin...........