Adding together separate currency rows based on adjacent column value

[lesliegiles]

Good morning

I hope you can offer any help or guidance on the issue I’m currently having.

As an example:

I have a spread sheet which totals the amounts in 2 columns D and E, which are pounds and pence. The formulas for each one are:

Pounds

• =SUM(D3:D8)+ROUNDDOWN((SUM(E3:E8)/100),0)

Pence

• =MOD(SUM(E3:E8),100)

These work very well and give me the correct figures.

What I would like to do is to add up the values of each row based on the criteria of column C and have just one value in ONE cell (as shown on lines 13 & 14), so for instance:

• When added together the values of rows 3,4 & 6 are 7.25 (based on a value of “W”)
• When added together the values of rows 7 & 8 are 5.00 (based on a value of “F”)

I have tried to show this as an example in the image below:

[TABLE="width: 579"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD="colspan: 2"]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD="colspan: 4"]RECEIPTS[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]DATE[/TD]
[TD="colspan: 2"]TRANSACTION DESCRIPTION[/TD]
[TD]TC[/TD]
[TD="colspan: 2, align: center"]IN[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD]W[/TD]
[TD]1[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD]W[/TD]
[TD]3[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD]W[/TD]
[TD]2[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD]F[/TD]
[TD]2[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD]F[/TD]
[TD]2[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]TOTALS OF ALL COLUMNS[/TD]
[TD]12[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]Total for all rows with "W"[/TD]
[TD][/TD]
[TD="colspan: 2"]7.25[/TD]
[TD](Figure recorded in just one cell)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]Total for all rows with "F"[/TD]
[TD][/TD]
[TD="colspan: 2"]5.00[/TD]
[TD](Figure recorded in just one cell)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I appreciate any suggestions you are able to offer and thank you in advance.

If this has been covered in another thread please do let me know

I’m using Excel 2010 on Win 7 Pro.

Kind regards

Les

 

[Eric Golf]

Hi,

In cell E13:-

=SUMIF($D$3:$D$8,"W",$E$3:$E$8)+(SUMIF($D$3:$D$8,"W",$F$3:$F$8)/100)

and in cell E14:-

=SUMIF($D$3:$D$8,"F",$E$3:$E$8)+(SUMIF($D$3:$D$8,"F",$F$3:$F$8)/100)

Hope this helps,

Eric.

 

[Eric Golf]

Hi,

In cell E13:-

=SUMIF($D$3:$D$8,"W",$E$3:$E$8)+(SUMIF($D$3:$D$8,"W",$F$3:$F$8)/100)

and in cell E14:-

=SUMIF($D$3:$D$8,"F",$E$3:$E$8)+(SUMIF($D$3:$D$8,"F",$F$3:$F$8)/100)

Hope this helps,

Eric.

 

[lesliegiles]

Hi Eric

Many thanks for your prompt reply. That works great and is exactly what I needed.

Thanks again

Les