Sum Totals From Another Worksheet When (a And (b Or C))
Jan 21, 2009
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.
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?
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.
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.
I have an excel sheet setup with several tabs which are organized by work days in a week. (i.e. aug 18, aug 19, aug 20, aug 21, aug22, aug 25, aug 26, aug 27, aug 28, aug 29) Sample file has been included.
I would like to do a rolling total of 1 particular cell (the same location on all the sheets, i.e. cell a2 on all sheets) for the past 4 weeks, and it automatically adjusts itself based on today's date (i.e. today is august 27, it will total everything from jul 30, 31, aug 1, 4, 5, 6, 7, 8, 11, 12, 13, 14, 15, 18, 19, 20, 21, 22, 25, 26.).
Is it possible to do this?
I am not familiar with VBA or Macros, so solutions using either of these will require some detailed explanation for me to understand how to apply them.
I have a file that tracks the number of days my employees take off each month. I want to be able to go back 6 months on a rolling basis to get the total number of days taken off.
I am trying to set my file up so that I can type in the month in cell R1 and have column S update for each employee with the total number of days he/she has taken off for the past six months. For example, if I type in OCT in R1, I would like to see S2 change to 6 and S3 change to 2.
Can we print out runing totals, i.e. for data on single worksheet at the time of printout I would like to print carried forward total at the end of the page and brought forward total at the starting of the next page
I’m no expert with excel, so I might be going in the wrong direction with this completely, but I thought I had this working right, until I applied it to the next month. when going from January to February, I get two entries of 400 that I don’t want there, I understand why I’m getting them, but not real sure how to fix the problem, I’ve done something similar to this in the past but maybe I used something other than vlookup for it, I can’t remember. I need a total for all months, but don’t know how many entries will go toward each month.
I need to create a running monthly total formula for a worksheet. This is something i need in the data, so i can manipulate it without using a pivot table.
I think I would use the SUM, MONTH, and IF formulas, and maybe EOMONTH.
I guess where I'm stuck is figuring out how to make conditions for my SUM formula. So as the dates go down the page, I would like a running total in a column to the right, that will also restart with every new month.
