Checking Accounting: Copy all "Gas" amounts to new column
Checking Accounting: Copy all "Gas" amounts to new column
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
Last edited by MrProgrammer on Tue Dec 03, 2019 1:03 am, edited 1 time in total.
Reason: Edited title, was: Checking Accounting
Reason: Edited title, was: Checking Accounting
OpenOffice4.0.1 on windows 8.1
- MrProgrammer
- Moderator
- Posts: 4909
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Checking Accounting
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.BJW86 wrote:I want to find …
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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Checking Accounting
I was wanting all the amount for Gas Company's to be put in column F1 the next in F2 and so on. Brad
OpenOffice4.0.1 on windows 8.1
Re: Checking Accounting
Try a pivot table : Data < Piviot Table > etc.
LibreOffice 7.3.7. 2; Ubuntu 22.04
-
- Posts: 351
- Joined: Sat May 24, 2008 6:59 pm
Re: Checking Accounting
What about an IF formula copied down column F
=IF(C2="Gas Company";D2;0)
=IF(C2="Gas Company";D2;0)
OOo 4.1.1. bld 9593 on MS Windows 10 64 SP1 Chillblast fusion i5 and Acer Aspire E1-572 Laptop
There's got to be a better way
And for all accountants - The change is coming
There's got to be a better way
And for all accountants - The change is coming
- MrProgrammer
- Moderator
- Posts: 4909
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Checking Accounting
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.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.
[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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Checking Accounting
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
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
Last edited by RusselB on Fri Nov 29, 2019 9:03 pm, edited 1 time in total.
Reason: Advertising URL removed to make the post eligible for posting.
Reason: Advertising URL removed to make the post eligible for posting.
openoffice 2.4 on ububtu 9.04