Excel Extraction of figures

Excel extract figures from rows in one column, if there is a specific word in a previous column

Didn't find your answer?

Hi

I wonder if anyone can help me to extract certain figures using a formula that only picks up a number in a column if the word at the side matches the request? Although the word in column E is always the first word on the row, there are usually different numbers and letters after the word in that cell.

Example

Column E Column F
Client 100
Prospect 20
Older 30
Prospect 50
Older 60
Client 80

To either give just the totals under headings for Client, Prospect and Older OR list the items under Client, Prospect and Older headings as:

Result
Client Prospect Older
100 20 30
80 50 60

OR if it is easier, just the totals as:

Totals
Client Prospect Older
180 70 90
   

 

Many Thanks

 
 
     
     

 

       

Replies (10)

Please login or register to join the discussion.

avatar
By johnhemming
18th Nov 2019 17:21

Sort it and use subtotals ... or

If there are a limited number of possible codes then use if() to put values into appropriate columns.

Thanks (1)
Replying to johnhemming:
avatar
By Carolynne
18th Nov 2019 17:42

johnhemming wrote:

Sort it and use subtotals ... or

If there are a limited number of possible codes then use if() to put values into appropriate columns.

Hello Johnhemming

I Have tried to A-Z it, but it then puts everything not in date order, just all the rows with Client in order as an example, and I was hoping there would be a way of creating a new table at the side with the original table being kept as it is.

I have tried an If statement, but just don't know how to tell it to look for a word in column E and if that word appears within a row, put the value of that row that is under Column F here (or total all the values in column F that have the word within the text on the rows under column E).

Row one of Column E might say Clients from Birmingham
Row two of Column E might say Clients from Manchester
Row three of Column E might say Prospect 123
Row four of Column E might say Prospect 156

The word is there, but not the only words in the cell

Thanks (0)
Replying to Carolynne:
avatar
By johnhemming
18th Nov 2019 19:18

You might be best emailing me an example I would do the changes and then post here an explanation [email protected]

Thanks (0)
Caroline
By accountantccole
18th Nov 2019 17:37

SUMIF ?

Thanks (1)
Replying to accountantccole:
avatar
By Carolynne
18th Nov 2019 17:58

Hi Accountantccole

I have just tried this formula

=SUMIF(E2:E273,"Client",G2:G273)

(In the sheet I used, the figures are in column G and words are in column E).

It just brings a result of zero '0'

I am not the most proficient with Excel, but I think that with there not just being the one word in the row, it can't see it

Thanks (0)
avatar
By paulwakefield1
18th Nov 2019 18:00

One solution is to use text to columns (on the Data tab) then delete all but the first of the new columns. You will then have just the first word. Turn it into a Table (Ctrl + T) and then filter or use a Pivot table. Or you could use If and filter out blanks.

Thanks (0)
Replying to paulwakefield1:
avatar
By paulwakefield1
18th Nov 2019 18:01

The problem with this is that Text to Columns is not dynamic so will need to be run each time the underlying data changes.

Thanks (0)
Replying to paulwakefield1:
avatar
By paulwakefield1
18th Nov 2019 18:06

So a better solution:

Turn your data into a table
Add a new column and type into the first cell of the new column =LEFT(A1,FIND(" ",A1)) where A1 is for instance Prospect 123 (Assumption: There is always a space after the first word). The rest of the column should self populate.
You can now filter for your target word or use a pivot table

Thanks (1)
All Paul Accountants in Leeds
By paulinleeds
13th Dec 2019 15:01

Use something like =IF(E1="Client",D1,0)

I'd put this formula into an analysis column, eg G1, for Clients and use columns H, I etc for headings of Prospect, Older, Client etc

If the word Prospect, Older, Client contain text before or after then use something like =ISNUMBER and the =SEARCH function in Excel to find cells containing specific text. See https://www.excel-easy.com/examples/contains-specific-text.html for the syntax.

Thanks (1)
Replying to paulinleeds:
avatar
By Carolynne
13th Dec 2019 17:09

Eureeka
I have finally got this sussed now. What I did was took on board the previous post and combined it with yours as follows:
I created a new column G and input an =LEFT formula as suggested by Paul Wakefield, and it inserted the first letter of each column so that I could then create a third column H whereby I could then use an If statement to select if the letter I wanted was in, put the monetary total.
It worked great!

Thanks to Paul Wakefield and Paul in Leeds. I have now completed this and will be of a big help going forward. I have clicked Thank and it is showing Unthank on screen, so I am hoping this means I have thanked.
Carolynne

Thanks (0)