I wish to total the number of occurences of a person's name on one worksheet and show the total on another worksheet. This is a mock up of the worksheet I want to take the totals from:
This worksheet will get longer each day - it's the names of pupils getting detentions in the school I work in. I want to display the total number of detentions for each pupil on a different worksheet like the one below:
I have been trying to use the COUNTIF function and then copying it to the subsequent cells in the second worksheet. However, each time I do this the range changes. Is there a way to lock the range so that it is the same in each cell?
I attach a couple of files that i receive a few times a year and wish to be able to count the numbers in a more effective fashion than i have attached. Essentially, the source data file naturally changes its numbers each time i get the file so the file that i tinkered about with (also attached) would need to have each and every formula moved to tally correctly. This seems to be a waste of time and would gladly appreciate any assistance. Columns A & C are irrelevant for the purposes of the exercise and can be ignored.
I have to be able to count the data according to employee number groups:
0-19 employees 20-49 50-199 200-499 500-25000
While I have already done this on the attached file you will be able to see that its a long drawn out process. Can anyone advise a better way?
I need and can't wrap my head around it enough to figure it out myself.
On one worksheet, I have raw sales data:
A B C D E F Cust # Cust Name Product # Prod Name Sales Date 10001 whoever 800001 whatever 200 1/3/2009 10003 whoever2 800241 whatever 250 1/3/2009 10001 whoever 800060 whatever1 210 1/3/2009 10001 whoever 800055 whatever2 222 1/3/2009 Each product has multiple product numbers, so one product could have multiple product numbers: 800001, 800055, 800241, 800002, etc..
I want to count total sales by product, regardless of customer for a certain date.
I have the product numbers in their own cells.
I would like to say "If the product number is any of these, and the date is this, add all the sales numbers together and give me a total"
I have a file I'm working with containing several worksheets and then a worksheet that totals all the data. What I would like to be able to do is write a formula so that when new worksheets are added, the data from the new tab is automatically captured on my totals tab.
I've tried using the formula below, but get the #NAME? error.
=Sum(Ashely Core Start:End!C7)
- in the formula, all the tabs I want to sum are in between Ashley Core Start and End.
Each petition can generate several tasks, one line per task.
John Doe | XXXX-YYYY | NCO John Doe | | RIL John Doe | XERT-WWWW | RMT Jane Doe | QSZE-AQWC | RIL
On the second worksheet:
Complete list of agents | number of petitions | Status
John Doe | 2 | OK Jane Doe | 1 | [BLANK]
I want to be able to fill in the second worksheet automatically. For each agent in my worksheet 2, I want to check if they appear in worksheet 1 and if so count the number of petitions related.
Having performed a subtotals function on a large worksheet, I want to simply copy the results of the "totals" rows to another worksheet but without all the hidden rows which are used for the calculation of the totals.
my 1st spreadsheet has the following details:1)cars,(2) date sold,(3)month sold (4)new ownerunder the heading for cars there are 5 different models. On the 2nd spreadsheet i enter on a weekly basis the cars that were sold for the week under each category example:
ford 5 toyota 10 mercedes benz 3 and so on
i would like to know if these totals can be added up using a formula from excel in the 2 nd spreadsheet using the data from the 1st spreadsheet? there are 12 months in the month sold column and 5 different car models. i need to know that for feb there were 5 ford's sold although january had 10 showing as sold ? small example herewith
cars new owner selling price month sold ford Mr.Z 25000 jan merc Mr.X 49999 feb toyota Mr.A 34000 feb nissan Mrs.B 12000 jan ford Mrs.C 23000 feb merc Mr.A 34000 jan toyota Mrs.D 21000 feb
Below is an example from the ozgrid forum illustrating the worksheet change event. Not sure of the meaning of 'Do nothing if more than one cell is changed" on the 2nd row of the code. Does it means that if a value is entered in other cells, the code will not fire?
Private Sub Worksheet_Change(ByVal Target As Range) 'Do nothing if more than one cell is changed or content deleted If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub If Target.Address = "$A$1" Then 'Ensure target is a number before multiplying by 2 If IsNumeric(Target) Then 'Stop any possible runtime errors and halting code On Error Resume Next 'Turn off ALL events so the Target * 2 does not _ put the code into a loop. Application.EnableEvents = False Target = Target * 2 'Turn events back on Application.EnableEvents = True 'Allow run time errors again On Error Goto 0 End If End If End Sub
I cannot get various formulas (Countif, Match, Frequency, Etc) to work properly.
I am trying to arrive at a total number of matches of numbers in cell range B1:G1 with any numbers entered into the cell range of K1:P11 and have the total of matches display in cell H1. However I do not want to count duplicate numbers from the K1:P11 cells. (if the number 5 in posted in K1:P11 multiple times I only need it reported once in H1)
B1:G1 is the constant and the numbers will not change - K1:P11 cells will be populated by adding numbers until the all the numbers in B1:G1 is completed and match.
I was given this spreadsheet to count attendance by entering the entry date and exit day, however it's counting the first day and the last. I'm needing it to only count the first day and not the exit day.Book2.xls
What I want the formula to do is look in Column A find all of the dates that are on the same day and then look in column G and add all of those $ amounts.
I want to add all these cells together for the total. I used the formula =SUM(B6+B8+B20) and it works fine. However, when I try =SUM(B6+B8+B20+B29) I end up with 0.00 in cell B31
I'm tracking 50 food items (in column A) for 40 homes (columns B-AO) where each home gets none or varying quantities for each food item (ex: apples 7 for home B, 0 for homes C-L, 19 for home M, etc.). I can get grand totals for each item for all homes, but need to also list how many for each home receiving apples. Ex: 4 homes get apples (4, 7, 19, 1 respectively). SO I want my total sheet to have a column for each food item with the grand total "31" and the second column to show 4 + 7 + 19 + 1. The quantities change frequently so I need a formula I can use for each food item. Also I don't want "zeroes" included. What formula would I use to do this?
What I want the formula to do is look in Column A find all of the dates that are in week # whatever and then look in column G and add all of those $ amounts. So the result would be...
I need help to total a column but in four cells the values are as a result of conditional formatting and the total ignores those cells. Is this difficult or am I a bit slow? This is in 2003.
This problem has come at the end of a big exercise whereby I've managed to construct a spreadsheet that automates three payment processes based on various VLookups to another file. However, I've come to a stage where I need to pre-empt a payment allocation based on cumulative totals.
I'll try and explain clearly below but I've also attached a spreadsheet showing the intended result (along with a copy without the output so that someone can add in the formula - again, if it exists.
So, this all centres around a code allocated to our clients and a declaration that they complete. Say client code "Apple1" (col E) sends in instructions to pay on a position of "650,000" (col F). Within our existing spreadsheet we have an available position of "800,000" which can be seen by totalling column G for client code "Apple 1".
So that's the first requirement - for all rows on my existing spreadsheet for client code "Apple1", I want to add the cumulative total of column G ("800,000") I'd like this captured in column H, as per my example.
We can then make payment based on their declaration, up to their total available amount. However, we need to pay them in stages according to our existing allocations (col G). Therefore, taking the first example, their declaration show's an available position of 650,000, their total position is 800,000 and the available nominal amount for that row (col G) is 200,000 - so they can be paid on that full amount therefore, 200,000 should be shown in cell I3.
So now, they've got 450,000 left to be paid and cell G4 again shows a position of 200,000, so once again, cell I4 should show 200,000.
The client "Apple1" now has a payment amount remaining of 250,000 remaining but in this instance, cell G5 is for 300,000 - so because the remainder of their available payment is less than the amount in cell G5, we should post the available payment amount in that field - 250000.
Therefore, if you add up all of column I for client "Apple1" it comes to 650,000 - the amount we have on our declaration - even though the total of their available position is 800,000.
I've included another client in the mix "Sauce2" who should show 200,000 and 0 respectively in cells I6 and I7.
I have a column of wickets (M). M5 and M6 are the number of wickets for one match, M7 and M8 for the next, and so on. Two wicket entries for each match.
I needed a formula to count the amount of times the combined wicket total for a match is greater than 10. Initially there were only a few M values, so I used this:
IF(M5+M6>=10,1,0)+IF(M7+M8>=10,1,0)+... and so on. Now I want to expand it to have more M values and this formula would become huge.
I just need the totals for each store, which I will then copy into my billing file. The person who sent me this original has already subtotaled it, and each site has a plus sign next to it. When I click the plus sign I can see the breakdown (ie store 50 ten times, store 60 twelve times etc) but otherwise the store totals are given on 'adjacent' rows. The problem is these rows arent actually adjacent, the other rows are just hidden. So when I try to copy the store totals all the other junk gets copied as well.
I would like to get the total in column B but its not giving me the correct total. So I would half to use a helper column in column D to retrieve correct results. Is there anyway I could have an all in one formula for this. I would prefer not to use the helper column. The correct result is in cell D23.
I would like to know how do I go about adding the following:
This is a test it counts out of 1250 points
But there are 3 fields which can be omitted from the grand total of 1250 Some students might not have all three fields. The fields totals are 30, 90 and 130. The students might have one or two of them in either order. Now what I would like to do is have 3 different fields where I can mark with a Y=Yes and N=No in a block. then it would change the Grand Total score accordingly.
I have a spread sheet that is used to review calls placed by a call center.
Column A has the extensions of the phones, and Column E has the type of call (Outgoing or Incoming). Each line is a new phone call.
We have about 8 extensions, but the worksheet could have a couple thousand calls. So, Column A could have extension 1401 from rows 1-100 as extension 1401 made 100 calls. I'm looking for a formula or macro that will summarize how many outgoing and incoming calls extension 1401 had. Thoughts?
My initial thought was something like this:
=SUMPRODUCT(--(A2:A5000=H2),(E2:E5000))
In this formula, I would type in the extension of H2 and it would scan Column A and add up the values in Column E. The only problem is, that Column H doesn't contain a numerical value. It only has "Incoming" and "Outgoing" (minus the quotes), so this doesn't work.
I am attempting to build a spreadsheet for work, and I'm having trouble calculating a correct total.
In essence, the bit of my spreadsheet I'm concentrating on is 2 columns:
Column A is headed "Type of Work" and Column B is headed "Completed".
The Type of Work will either be Letters or Memos, and the Completed will either be Yes or left blank to indicate No. For other reasons, No has to be represented by a blank cell.
At the moment, I have 12 rows, going from A2 to B13, with data such as the following:
I need a summary section at the side with the following calculations:
Total Number of Entries Total Number Outstanding Total Number of Letters Outstanding Total Number of Memos Outstanding
However, as I have only populated it with 12 rows of data for my testing purposes, I need to build formulas to take into account the fact that a maximum of 2000 rows may be filled in by other people over the next few months. Therefore, my formulas look like the following:
Total Number of Entries (stored in E2): =COUNTA(B3:B2000)
Total Number Outstanding (stored in F2): =E2-(COUNTIF(C3:C2000,"Yes"))
Both of the above formulas work perfectly, and when I enter a 13th and 14th row, the totals update as I want them to, so I have no problems there.
However, I am struggling to enter a correct formula which will calculate the total number of Letters that aren't Completed.
I did set some names and tried to use: {=SUM((Type="Letters")*(Completed="Yes"))}
but it gave me a #NA error, presumably because the named range was looking at all the empty cells up to A2000.
I am currently trying to find a better way to track quantities of delivered material from multiple suppliers. I have a attached a sample of what I am trying to do. In the "totals" sheet Row 3 is working as planned. There are a couple of issues that I am having with my formatting as is. First off in my "raw" data sheet there is an empty row between each row of numbers (this is they way I am sent the information from the supplier). This empty row causes my totals to place a Q everywhere column A on "Raw" is not filled with "B". Is there a way to have the totals sheet only pull from the cells with values in them?
My other question is how I would be able to get the values for material, Net wt., and Charges to populate if Column A in "Raw" is Q?
I have a list of stores and the costs for various items.
Each store has (usually) more than one row.
Each store's items are totaled at the end of each line. Simple
What I do not know how to do is total all rows for each store, automatically. Like I said, I don't know how to explain it, so searching for it does not work out well for me.
Please see the attached sheet : How to Calculate Store Total.xlsx
I would like to take all of the data from student summary sheet for each category and total it for all males and females separately on the second sheet. I am looking for a formula that i can put in each cell and calculate all of the totals, and then just drag down....
I need to make a program to take inventory at a bakery. We do this twice a day. Our products are loaves of bread. We have white, multigrain, soy and lindseed, low gi, and gluten free.
They are in pallets, trolleys, crates, boxes, pallet rows (part of a pallet) and individual loaves (singles)
I need to find out how many loaves that we have. How do I total them up?
I basically need to add the ongoing totals for each house team-so when scores are inputted, the house teams score is automatically being added up.....I think I would need to mail someone the sheet to show how far I have got as I dont think it is a simple count formula.
