Sumif for a certain format

[bwinman]

How do I add up values in a particular format and ignore values in other format. So say I have a row with values of dollars($) and sterling(£) but I only want to know the total of the dollars at the bottom of the row and ignore the sterling (£).

Also say I have a row with percentages(%) and normal numbers. If I only want the total of the figures in percentage format. For these two problems what formula would I use?

 

[wigi]

Hello

I would suggest to not try to do things like this.
Just use another column where you indicate the currency.
Then you can sort, filter, add up, ... do anything you want.

 

[bwinman]

Hello

I would suggest to not try to do things like this.
Just use another column where you indicate the currency.
Then you can sort, filter, add up, ... do anything you want.

Actually it was part of an exam on excel which i stumbled on. If you don't know why even bother to answer.

 

[MARK858]

why even bother to answer.

The reason Wigi answered is having mixed types in a single column is a really bad spreadsheet design and makes handling data difficult so it is bad practice (especially as part of an exam ).

Anyway to your actual question (at least the 1st paragraph) is the content of the cells actually different formats or are they actually strings with the currency symbol at the front (to test in another cell put =ISNUMBER(A1) where A1 is the cell you are testing, does it return #FALSE?

or click the cell and look at the formula bar, does the currency symbol show there?

or if you haven't changed any alignment is the cell aligned to the left.

If they are text then there are relatively simple formula but if they are actually different formats then you would probably either need to use vba (or at least vba to make a UDF), and as none of the options for different formats is straightforward then that is where you really do need to see Wigi's response.