Free Microsoft Excel 2013 Quick Reference

- Sum of different items in the same column
- Find sum of different colums in different tables
- Summing of numbers between sheets for same column values
- Counting Items
- List of non-duplicated items in a range and their sum
- Diff challenge - need to count values/items in rows depending on several criterias beeing met at varying cell references/places
- Summing similar items
- List specific worksheet and use in VBA formula
- Summing on multiple conditions
- Pivot table calculation for difference between 2 columns &sources
- Formula: Counting # of month+year in one column based on the month give in another
- Bringing together whole rafts of info...
- Sum of multiple Vlookups...
- Subtotal by two columns at once
- Vba Excel code for inclusion in existing vba project
- Conditional summation of like entries in a table
- Pivot Table - Input 2 items into Data Area
- Sum all data and copy
- Using Indirect in a Sumproduct (& pivot tables)
- Sum column 3 based on only unique values in columns 1 and 2 using an array formula

I hope my attached sample file will give you a good idea of what a want to achieve.

Thank you

i have got about a hundred tables with the same format on the same worksheet.. i wish to find the sum of the values in different columns with the same TITLE.. i know the easy way would be to add them all but the problem is that the data is quite alot so i would like a formula that would make things easy for me..

http://s14.postimage.org/pco2b9nj1/subtotal.jpg

There are over a hundred such tables and i wish to find the total number of items on a particular date.. is there any formula to do such a thing?

system. Each system has multiple items in it with different yearly

quantities.

For an example, in sheet1 I have a table with 3 columns A- is the item#

(00010001), B- is the system that Item # is placed in (BB001), C- is

the quantity of items in that given system (111000). In sheet 2 I would

like column A to be each system (BB001-BB100) and column B to be the

sum of all of the different items #'s in that system (lets say system

BB001 has 3 item #'s 00010001, 00010002 and 00010003, all three of them

have a quantity of 111000).

What function or formula would give me the correct sum of 333000 for

system BB001? How would I make this dynamic so that if 2 more items

were added to system BB001 their quantities would be included in the

overall sum of quantities in that system?

I have a number of strings in a spread sheet in the same column on multiple sheets. There are about 40 different strings. For each string I need to cumulatively sum up a corresponding long value on the same row.

E.g. string = HIU value = 7282

string = HIU value = 4

so I'd get 7286 etc.

I need to get in a new worksheet the total quantity per item per site without duplicates:

This is a sample of the data, basically it indicates the items and their quantity per day and can be up to three different items in one day.

******** ******************** src="http://www.interq.or.jp/sun/puremis/colo/popup.js">*********>Microsoft Excel - Book2___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA5=

ABCDEFGHI5 Date1Date2Date5Date6Date7Date8Date96Site152D38352D38352D39352D38252D38252D38252D3937854588838 46D3A12 46D324 9 3 8 10 52D382 52D393 11 8 3 12Site252D38352D38352D39352D38252D38252D39352D39313775775514 52D393 15 5 16 52D393 17 5 Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

the new list should look, something like this:

******** ******************** src="http://www.interq.or.jp/sun/puremis/colo/popup.js">*********>Microsoft Excel - Book2___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB21=

BCDE21 ItemQty 22Site 146D3248 23 46D3A123 24 52D38232 25 52D38313 26 52D39351 27 28Site 252D38214 29 52D38314 30 52D39325 Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR. Hope I made myself clear.

I'm not really a pro on excel matters, just trying to make my job easier, I'm eager to learn of the benefits of VBA, and have done some routines mostly modifying recorded macros. Any help to point me in the right direction is greatly appreciated.

Thank you!

Bellow is an extract of a much much larger set of data.. but this is a repetitive process and all I need is something that will work within a set range of 12 cells/columns in a row and 7 rows down. (The problem is described below the figure.)

******** ******************** src="http://www.interq.or.jp/sun/puremis/colo/popup.js">*********>Microsoft Excel - MAL6_of_Treningslogg_KUI_Analyse_(version_4_7).xlsx___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=

EFGHIJKLMNOPQ52163xxxxx2xxxx7xxxxxxxxxx28xxxxxxxxxx29xxxxxxxxxx210MAL

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Row 5 (Grey row) -F to Q is a constant/set reference row that all the other rows below need to refer to. The values that occur here is numbers from 1 to 5. (Which again is code/synonym for a set text name that only relates to that specific number.)

Row 6 - 11, column F to Q contains variable data.

I need to count the amount of "3"s and "X" individually for each row. But here comes the challenge.

The amount of "3"s an "X" are only relevant if the grey row has a reference number 1-5 in in it and the corresponding cell below, (belonging to an individual row), have a 1 or 2 in it. This means that Im collectiong this data for each of the reference numbers in the grey row.

For example: Looking at the image above: I need a formula that will first check the grey row, (row five), within the set range Column F to Q, cell by cell, for "1"s. If this first criteria is met then it will check the cell directly below for the first row, two down for the second row etc. for 1 or 2. (>=1). If this second criteria is met, then I need it to count the amounts of "3"s and then the amount of X's within this rows range F-Q, (And in such a way so I can get the result in two different places - one for 3s and one for x's).

In the example above, it would not count anything for 1's because the value >=1 is not in a row below the 1 in the grey area.

But when the calculations started checking for 2's, the first criteria would be met in L5. The second criteria will be met in the cell L6, and then the formula I need should count amount of 3's in the range Row 6A, column F to Q. This should then be repeated for the counting of X's.

I need to do it this way because the result Im after is for example.

How many 3s do I find in a row that relates to the reference number 1,, (or 2,3,4to 5). How many X's. And this is to be summed up for each row, within the specified range. And the data should only be added up if the first two criterias are met for each individual reference number in the grey row.

For each referencenumber within a set range i need to know the exact amount of 3's within the specified row, and the exact amount of X's within the same row, related to a specified reference nr.

An example result from the example above could be:

Ref nr 2, Row 6A (range F-Q) , 1 times 3

Ref nr 2, Row 6A, (range F-Q), 9 times X

I really really hope somebody can help me out or can recommend a good reference source so I can solve it my self.

I am very happy I found this forum and are looking forward to learning lots more about excel.

Ruth

I have an excel workbook that will contain about 20 worksheets. The 20 sheets are similar in layout but have different numbers of rows and different numbers of column repeats (see below).

Each spreadsheet has less than 1000 rows and 32 to 44 columns.

There are 3 columns that repeat between 4 and 8 times.

These 3 columns are: item, quantity (for each product) and total items per product (total items is calculated).

Each item may be in any one of the repeats.

I would like to be able to sum the total items per product of all items with the same name within a selected area and within the whole sheet. These sub-totals I'd like to keep on the sheet with the other information but at the side as a list of items and the sub total for each item.

I'm a newbie to excel and don't have a clue where to start to do this.

I'm sorry if this description isn't completely clear and that the attached part spreadsheet helps. Please ask me and I'll try to clarify specific points.

I'd greatly appreciate some help.

The summary sheet is Sheet9, tab name Complete TC Summary

Totals will be created from various columns in each Results sheets

These Result sheets have identical column structures, just containing different data and worksheet names

There could be many Result sheets that include, and come after (to the right of), the Current TestCycle Results worksheet

No other sheets apart from Result sheets come after the Current TestCycle Results worksheet

Requirements:

From A4 of the Current TestCycle Results needs to list all worksheet names of the Results sheets (no other sheets)

Adjacent to each worksheet name will be a count of items from that particular worksheet. For example in A5 will be counting all the words 'Fail' in column P from every listed worksheet, in column N & O a sum totals durations and returns the value for every listed worksheet

I have attempted this without any joy. Cant seem to only list specific worksheet names although ws. could be used in a VBA formula. File attached.

Any help or tips greatly appreciated :-)

Here's what I've got:

1) COUNTRIES

2) PRIORITY

3) COMPONENT (Y/N)

4) TOTAL QUESTIONS - This is a column containing numeric values based on the

survey the end user received

5) QUESTIONS ANSWERED

7) PROGRAMS

I need to know the following:

Count of PROGRAMS for each COUNTRY with a specific PRIORITY and COMPONENT *

Sum of TOTAL QUESTIONS meeting the Count of criteria.

Number of QUESTIONS ANSWERED where the count of PROGRAMS for each COUNTRY

with a specific PRIORITY and COMPONENT

In a nutshell, I have 16 different countries I'm trying to collect data on.

I have all my raw data in a tab other than where my formulas are. I know

(for example) there are 10 programs for 'India' that are 'priority 2'

'infrastructure'. I have a column that is labeled 'total number of

questions' - this is the number of questions on the survey given. I know

there are 50 questions on this survery, so 50 questions*10 programs= 500 -

but, how do i get this automatically without having to go figure it out

manually?

The second item is the same things as above (10 programs for india that are

priority 2 infrastructure. 50 questions on the survey.) but, I also need to

know out of the 'total number of questions' for all 'priority 2'

'infrastructures', how many questions have been answered to date. India has

answered 250 of the questions out of 500. But, this is across 10 programs.

Anyone have any advise here?

difference between two data sources for the same time period. Any help is

greatly appreciated!

Data Source

Sum of Jan

Item Desc Jan 06 Jan 07 Difference

Test 1 ABC 9,553 1,000 -8,553

Below is an example of the worksheet I'm currently working on. Currently, there're about 3000 records in the table. Here's what I would like to calculate, and having a hard time figuring out how:

For example, for August,2011 (column A) count how many items in column B is from January 2008. So,I'm ignoring dates comletely, only month and year are relevant. The answer should return number 2. Or if we counting it for for Feb 2011 it should return 2...Same should work for all other month(and other dates) in column A. So for July,2011 the answer should be 0,and for June,2011 it should be 1A B Disposition Date Commenced Date 08/01/2011 01/01/2008 08/01/2011 01/20/2008 08/02/2011 02/03/2009 08/03/2011 02/07/2010 08/03/2011 02/03/2010 07/03/2011 03/25/2011 07/01/2011 05/04/2008 06/01/2011 01/15/2008 06/03/2011 05/01/2008

Any idea how to calculate it? I tried everything from COUNTIF to different SUMs and other basic formulas....

I've just been posted to a new team in work who deal with sections of the accounts, namely method of receipts of payments. The main spreadsheet is organised as follows:

- two seperate strands of the business each have their own sheet

- the two sheets are designed in the same format

- across the top we have column headers relating to time periods (more to follow), and then the different streams of business. The time periods consist of seperate columns for financial year, month, and quarter

- for each month of the year, a 'template' is copied and pasted into the spreadsheet from another worksheet, containing all the entry items, eg Direct Debit, BACS etc. These are pasted in the first blank row available. Then the data from invoices etc is entered.

- there is a unit charge and total cost at the end of the row

Now this copy and paste has evolved over the 3 years this sheet has been in operation. It was originally 6 rows big, at one point was as large as 12, is currently 9. The variations come from changes in the way payments are handled.

Anyway, this system works and so they've all resolved to keep it as the inputters are heavily resistant to change

They've asked me to develop a sort of reporting spreadsheet that will allow them to track YTD performance against budget, previous years etc. Previously, the way they did this was to use filters to get down to the info they needed, and then create a new spreadsheet from that - there are hundreds of reports on the shared drive created in this way.

What I've set up so far is a series of tabs each relating to a financial year (Apr-Mar). Now I want to extract all this info from the 2 worksheets. I've set up a model template for each year, which contains all the items that exist, for every month.

Now obviously I could manually go through and extract this info, but I want to build a system robust enough to handle what happens in the future also, i.e. future months' entries. The problem is, I don't know where these entries will be as the size of the pasted element changes.

So far, I've been playing around with array entering the formula using SUM, IF and AND. I had some success in 'single row' entry, but once I try to get it working for an array, I hit problems.

Let me try to explain what I have tried to do.

Column A contains the names of the individual transactions. Columns B-D contain the data Month, Qtr and Financial Year. E-J contain the seperate accounts. Rows 11-82 contain all the transaction types (12 sets of identical data).

In my new sheet, in E11, I tried the following:

=Sum(If ( And ( A11:A82 = a11:a82, B11:B82 = b11:b82, C11:C82 = c11:c82, D11:D82 = d11:d82), E11:E82,0))

This is array entered into e11:e82. (For ease of demonstration, I've used capital letters to refer to cells in the original data entry sheet, and small letters for my own consolidation sheet)

I thought this would have the following effect - it would check whether the values in the data entry sheet match those in the consolidation sheet for each row in columns A:D. If it did, then it would post to that row in column E, whatever is in that row in the original sheet. So for example, if the row in the original sheet was for BACS entries, from April, of Quarter 1, of FY 05-06, then it would copy the value across. If not, it would enter 0.

However this comes up with an error (non-specific error).

So could anybody please suggest an alternative method of doing this.

I realise my explanation might be hard to follow so happy to provide the sheet if required...

Thanks in advance! :-)

Name Item-1 Item-2 Item-3 Item-4 Vlookup-Item-1 Vlookup-Item-2 Vlookup-Item-3 Vlookup-Item-4 Total

a 1 3 5 7 5 12 22 22 61

b 2 4 6 7 17 27 51

c 1 5 5 22 27

d 2 4 8 7 17 17 41

Item Price

1 5

2 7

3 12

4 17

5 22

6 27

7 22

8 17

I want to end up with the Total column without having to do all the different Vlookup's (Vlookup-Item-1 thru Vlookup-Item-4) and summing up the result.

Vlookup is "IF(B2>0,VLOOKUP(B2,$A$10:$B$17,2,FALSE),"")"

Any insight would be greatly appreciated.

Sudheer

I need to subtotal two separate columns at the same time in Excel. I have

item numbers in one column and the corresponding quantity of each item in

another column. Each item has many different rows. Using the Subtotals

command under Data, I am only able to subtotal by the item number and then I

have to go in manually and sum the quantities of each subtotaled item. Is

there a way using Subtotals or something else to subtotal by item number and

then sum up the quantity column related to each item number at the same time?

Thank you,

Katherine

I have an automatically generated workbook which forms part of a bill of materials which is generated from within an autocad drawing via vba. (see sample attached). This is only a small sample as some of these worksheets can contain many more rows of data. (Note the first 9 rows of the worksheet are auto filled by the same vba macro in Autocad to show project information from the drawing titleblock)

What i would like to do via vba is as follows

for every instance of 1,2,3,4 etc in column B i would like to automatically sum the corresponding values in column H and place them one under the other either at the bottom of the worksheet similar to as shown on sample or on a summary worksheet within the same workbook.

Columns J,K,M,L & O are part numbers and for each pour number (1,2,3 etc) i would like to be able to sum the number of different items required. Note the Part numbers will vary on a pour by pour basis.

I suspect a separate summary sheet would be the best option showing the same project information on the top 9 rows then going into detail with the summary information for each Pour Number in the sheet BOM. I have creted a basic example of what it could look like on the summary worksheet in the attached file.

I am not very experienced and have not used excel a lot in the past so would appreciate help in finalising my project. If anyone has some code they are willing to share it would be greatly appreciated.

Regards John B

I would like to ask for your help with the following problem.

I have a table showing the quantity of products that were sold to stores within a particular week. The following data are shown in each column: Distribution centre (DC) ID, Item ID, Item description, Store ID, Day of delivery, Invoice Number, Quantity delivered - see example attached.

I want to sum up the quantities of identical items that were delivered to the same store from the same DC. In other words, the following conditions have to be met: (a) Distribution centre ID has to be identical, (b) Item ID has to be identical, (c) Store ID has to be identical. To give a specific example as per the attached file, instead of having 2 rows for 2 deliveries of 5 + 3 boxes of chocolate to store# 1, I want to have just one row showing that store# 1 had 8 boxes of chocolate delivered, a second row for that store would show that store #1 had 10 boxes of tea delivered to it. I would like the summed up quantity data point along with the Distribution centre ID, Item ID, Description, Store ID, Day of delivery, Invoice Number to be in a new tab, i.e. I want to reduce the table size and do not want the original rows to be in the new table.

Since my table goes all the way down to the last row 65536, I would need the formula/macro to be able to recognise identical DCs/items/stores and then sum up the quantities (there are about 1500 different item IDs). Can you think of a solution??

Many thanks in advance for your help!

Arina

but I can't figure out the section where it finds the percentage of the order amounts. For some reason, I an unable to add the "Order Amount" more than once into the Data Area but this tutorial clearly shows it is possible.

Also, when i initially added the order amount into the Data Area, it displays differenty from the tutorial. Instead of what is shown in the example, my pivot table shows a section saying "Sum of Order Amount" above the "Salesperson" row and a column next to it saying total.

I think this is where the error is at.. i have double checked and tried several times, and even manually clicked the Order Amount to "Add to - Data Area" but the same thing occurs.

I simply cant add two items (The two different "Order Amounts") into the Data Area.

Does anyone know what I am talking about?

I am new to VBA, working on a worksheet and stuck to resolve following query.

I have a worksheet with important data that we update daily and just want to add a column at the end of the sheet.

Requirement:

Have Raw data in other sheet and want VBA to first find and sum similar variables (unique code is defined for each item). Copy them to another place or if they can directly copied to the origional sheet in the required column.

Example:

Raw data

Data is in different columns (e.g. code = Column D, Description = column F and qty = column Q)

Now what i want is it find and sum all the items with same code and make a new sheet like below:

Code Item description Qty

and with the help of vlookup (or any other way through VBA), it copies the Qty data comparing code to the actual sheet in the column added at the last of the sheet. One thing i am concerned with i dont want to increase the size of my origional file so planning to practise it with VBA rather than vlookup just to reduce the size and routine efforts.

I hope i have communicated my question properly and is easily understandable, if you feel something confusing please ask. I am looking for its solution i am not a business professional but have to do it and it seems difficult for me. Thanks

I have a sumproduct formula (i.e. in Worksheet1) which adds up amounts in one column (Column F in Worksheet2) if the values in 3 other columns (A, C and D of Worksheet2) equal the values in the corrosponding columns of Worksheet1.

The intention is to merge 2 reports together with the same column data except for the last column which holds dollar amounts which should be kept separate from the other report. Thus both these amounts would appear side by side in the merged report. Of course, not all items (e.g. identified in column A) appear in both reports, and many items appear multiple times, but differentiated by the data in Columns C and D (so should be kept separate until a further report calculates the totals). And any entries in Column F with the same values in Columns A, C and D is summed.

I use a Filter to copy and paste only unique rows so I get all rows with different values in Columns A, C and D with no duplicates.

I thought of doing a pivot table for this, but I can't seem to figure out how to get pivot tables to have multiple results columns which sum different column ranges (not to mention columns from 2 worksheets which shouldn't be lumped together into one column). Suggestion for pivot tables would be great.

Back to my sumproduct formula:

This is the formula that sums all values in Column F where the criteria in Columns A, C and D match:

=SUMPRODUCT((Worksheet2!A2:A9=$A10)*(Worksheet2!C2:C9=$C10)*(Worksheet2!D2:D9=$D10)*(Worksheet2!F2:F 9))

And it works (as far as my brief testing could determine). The problem is when one of the values in Column F is blank. The above formula works when there's a blank value. But not when I use the INDIRECT function to obtain the range. The reason I do this is because SUMPRODUCT cannot (for some reason) use open-ended column ranges (e.g. F:F), so I manually build the column range using COUNTA to get the last row and putting the result (e.g. "Worksheet2!F2:F9")into another cell (e.g. into I16).

Using INDIRECT($I$16) (and the other applicable ranges) in SUMPRODUCT works. Unless one of the values in Column F is blank (as opposed to zero). And this then results in a #N/A error for all results.

Any ideas? Thanks.

First post ever, I am completely stuck and I hope someone can help me out of the mess I'm in

I have 3 columns of data: Item number, purchase order number, and quantity. Item numbers and purchase order numbers repeat sometimes, so what I need to get is really only the first instance of the Item # and PO#, then add it to each unique instance of the same Item # but different PO# once:

Item PO Qty Total Qty on all Purchase Orders for item number listed in Row 1

1 101 100 300

1 101 100 300

1 102 100 300

1 103 100 300

2 104 100 200

2 105 100 200

3 106 100 100

4 107 100 100

5 108 100 200

5 108 100 200

6 109 100 100

7 110 100 200

7 110 100 200

The total sum for each item number of all unique PO's would be listed in the 4th column. the key is that repeat purchase orders must be excluded, so for item #1, the PO is listed twice, but I cannot count that as two hunderd, but only 1 hundred, then add that to the next qty on PO 102 and 103, because those are unique PO's - then total up the total value between those PO's for that item, and insert in column 4 by I guess doing a vlookup for that item?

If anyone knows how to solve this, that would be very much appreciated.

Thanks.

Jerry