Page 1 of 1

Checking Accounting: Copy all "Gas" amounts to new column

PostPosted: Sun Apr 19, 2015 7:59 pm
by BJW86
I got my check book on a spread sheet. So in column C is How the check is for "Gas Company" in column D is the amount for that check. I want to find all amounts for Gas Company and put them in column F. I have tried LOOKUP but to no luck any help. Brad

Re: Checking Accounting

PostPosted: Sun Apr 19, 2015 8:07 pm
by MrProgrammer
BJW86 wrote:I want to find
Do you mean that you want to sum all of the Gas Company amounts? If so, use SUMIF. Read about that function in the Help, in this forum, or in the Wiki.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

Re: Checking Accounting

PostPosted: Sun Apr 19, 2015 8:12 pm
by BJW86
I was wanting all the amount for Gas Company's to be put in column F1 the next in F2 and so on. Brad

Re: Checking Accounting

PostPosted: Sun Apr 19, 2015 8:34 pm
by jrkrideau
Try a pivot table : Data < Piviot Table > etc.

Re: Checking Accounting

PostPosted: Mon Apr 20, 2015 11:23 am
by Richarda44
What about an IF formula copied down column F
=IF(C2="Gas Company";D2;0)

Re: Checking Accounting

PostPosted: Wed May 06, 2015 6:27 am
by MrProgrammer
BJW86 wrote:I was wanting all the amount for Gas Company's to be put in column F1 the next in F2 and so on.
If you want view all of the amounts in column D with "Gas Company" in column C, this is done with a filter. You should use Data → Filter → AutoFilter. You could copy this data to column F, but it is not simple. See [Tutorial] Sorting and Filtering data with formulas.

[Tutorial] Ten concepts that every Calc user should know

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

Re: Checking Accounting

PostPosted: Fri Nov 29, 2019 3:29 pm
by pallavi
You can have as many columns as you like in your Table, just so long as you follow the ‘left to right’ rule above.

The ‘Table’ you are looking up can be in the same spreadsheet. Or a different sheet in the same workbook. Or in a different workbook altogether.

The table doesn’t have to be sorted in any particular order, but you must not have duplicates. Unless the information on each duplicate is exactly the same. For example, if Doug appeared twice in our Commission Rates table with different percentage rates for each instance, VLOOKUP would return the rate on the first instance of Doug.

The formula isn't case sensitive, so 'Doug' could be 'doug' or 'Doug', in either column B or the table.

What does it mean when my VLOOKUP returns a #N/A? It means Excel can't find the value you're trying to look up in your table. If you get this, but you can ‘plain as day’ see it's there in the table, then it’s likely you’ve got one prefixed with an apostrophe. To check this go to each cell you're referencing and look in the formula bar and see if there is an apostrophe in either cell ‘. You can only see the apostrophe from the formula bar. See example below.
Basically, Excel reads text prefixed with an apostrophe as different to text without. Even though on the face of the spreadsheet they might look the same. You need to make sure both the value you're looking up, and the value in the table either both have the apostrophe, or both don't. The quickest way to get rid of the apostrophes is to do ‘Text to Columns’. Or run it through the VALUE function, which converts numbers formatted as text to actual numbers.
Regards