Calculating Dollars not GBP in a row

[piggylumps]

Is there anyway I can calculate the sum of a certain currency in a row while ignoring others?

Say for example I have a range of a1:d1, a1 will be $25, b1 will be £36, c1 will be $75 and d1 will be £5.

So what I'm looking for will be a formula in e1 which tells it to sum the range only if the numbers are entered in $'s.

Hope that makes sense.

 

[pagrender]

I think there's a way to do this.

Can you have a "helper" column? For example, put this formula in B1 and drag it down: =CELL("format",A1)

Then, in a blank cell, enter this formula:=SUMIF(B1:B4,"C0-",A1:A4)

This worked when I tested it. Does it work for you?

Also, I'm sure someone can convert this to an array so you don't need the helper column.

Pete

 

[piggylumps]

I tried the following array to no success:

{=sum(IF(RIGHT(A1:D1)="$",--SUBSTITUTE(A1:D1,"$","")))}

All that did was return a zero value much to my annoyance, I'll have a look at your way now though. Thank you

 

[pagrender]

Looks like we may have to adjust the SUMIF formula.

=SUMIF(B1:B4,"C0-",A1:A4)+SUMIF(B1:B4,"C0",A1:A4)+SUMIF(B1:B4,"C2-",A1:A4)+SUMIF(B1:B4,"C2",A1:A4)

According to Excel help, here is a breakdown of all the formats so we may need to add anything that starts with a "C":
<TABLE class=collapse><TBODY><TR class=trbgeven><TH>If the Excel format is</TH><TH>The CELL function returns</TH></TR><TR class=trbgodd><TD>General</TD><TD>"G"</TD></TR><TR class=trbgeven><TD>0</TD><TD>"F0"</TD></TR><TR class=trbgodd><TD>#,##0</TD><TD>",0"</TD></TR><TR class=trbgeven><TD>0.00</TD><TD>"F2"</TD></TR><TR class=trbgodd><TD>#,##0.00</TD><TD>",2"</TD></TR><TR class=trbgeven><TD>$#,##0_);($#,##0)</TD><TD>"C0"</TD></TR><TR class=trbgodd><TD>$#,##0_);[Red]($#,##0)</TD><TD>"C0-"</TD></TR><TR class=trbgeven><TD>$#,##0.00_);($#,##0.00)</TD><TD>"C2"</TD></TR><TR class=trbgodd><TD>$#,##0.00_);[Red]($#,##0.00)</TD><TD>"C2-"</TD></TR><TR class=trbgeven><TD>0%</TD><TD>"P0"</TD></TR><TR class=trbgodd><TD>0.00%</TD><TD>"P2"</TD></TR><TR class=trbgeven><TD>0.00E+00</TD><TD>"S2"</TD></TR><TR class=trbgodd><TD># ?/? or # ??/??</TD><TD>"G"</TD></TR><TR class=trbgeven><TD>m/d/yy or m/d/yy h:mm or mm/dd/yy</TD><TD>"D4"</TD></TR><TR class=trbgodd><TD>d-mmm-yy or dd-mmm-yy</TD><TD>"D1"</TD></TR><TR class=trbgeven><TD>d-mmm or dd-mmm</TD><TD>"D2"</TD></TR><TR class=trbgodd><TD>mmm-yy</TD><TD>"D3"</TD></TR><TR class=trbgeven><TD>mm/dd</TD><TD>"D5"</TD></TR><TR class=trbgodd><TD>h:mm AM/PM</TD><TD>"D7"</TD></TR><TR class=trbgeven><TD>h:mm:ss AM/PM</TD><TD>"D6"</TD></TR><TR class=trbgodd><TD>h:mm</TD><TD>"D9"</TD></TR><TR class=trbgeven><TD>h:mm:ss</TD><TD>"D8"</TD></TR></TBODY></TABLE>

 

[piggylumps]

How do you attach a screen dump of the sheet your working on so I can show you the issue I have. I've tried using your solution but I can't get it to work, I think I'm having a thick day today

 

[pagrender]

Here is a link to the method I use at home (I'm at work right now so I cannot use this application).

http://www.excel-jeanie-html.de/index.php?f=1

 

[jasonb75]

Is your default currency USD, GBP, or something else?

 

[piggylumps]

I don't really have a default. I have three currencies I'm working with, US $, GBP £, and Euro. As such I have a coloumn at the end of the rows to calculate each, so lets say that e1 will calculate every occurance of a $ in a1:d1, f1 will calculate every occurance of a £ in a1:d1 and so on

 

[jasonb75]

In excel press Alt + F11, then in the menu, Insert > Module.

Copy the code below, and paste into the Module.

Public Function SUMCURR(rng, curr As String)
Select Case curr
    Case "USD"
        curr = "409"
    Case "EURO"
        curr = "€"
    Case "GBP"
        curr = "$#,##0.00"
End Select
    For Each cell In rng
        If InStr(cell.NumberFormat, curr) Then
            SUMCURR = SUMCURR + cell.Value
        End If
    Next
End Function

Close the Visual Basic editor and return to excel.

Use the formula for the appropriate currency

=SUMCURR(A1:D1,"GBP")

=SUMCURR(A1:D1,"USD")

=SUMCURR(A1:D1,"EURO")

I've made some assumptions based on the formats used, so it may need editing if the results are not as expected.

 

[piggylumps]

That has resulted in the closest yet, on some rows it works and others it doesn't, but you've given me something to play with, thank you