SUMIF Function: Calculate What Is The Total For An Individual For The Whole Month
I have the same data (but not necessarily on the same cell for everyday) for everyday of the month. Now I need to calculate what is the total for an individual for the whole month. Example,I need to total John's data for the month of January from the individual sheet for Region 1, Region 2 and region 3. I tried the sumif function as below but it does not seem to work. =SUMIF('Jan 1:Jan 3'!B5:B7,"John",'Jan 1:Jan 3'!C5:C7). I have simplified the file as an attachment below.Appreciate need as the original file is for 6 months and I need a way to compute the data.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Date Function Query (calculate The Last Day Of The Month)
I am using a formula to calculate the last day of the month, using any date of the month in a worksheet in cell A13, this cell is also linked to another worksheet to pick up a date, using the ISBLANK function to prevent a dummy date entry appearing if the field in the linked ASHBY RISE worksheet is blank =IF(ISBLANK('ASHBY RISE'!$C$5),"",'ASHBY RISE'!$C$5) The last day of the month function is shown below =DATE(YEAR(A13),MONTH(A13)+1,0) This works fine if there is a date in A13, but returns a #VALUE! error if cell A13 is blank. I have tried using the ISBLANK function, but I am still getting the #VALUE! error. Of course I may have the sysntax incorrect.
View Replies!
View Related
Total Occurences: Formula That Shows The Total Payments Recieved For A Particular Month For A Particular Product
I have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell. Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example. I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.
View Replies!
View Related
Automatically Bold And Highlight The Current Month’s Total And Month Name
I have a spreadsheet for monthly supplies. In row 1 is Jan – Dec and in the row 2 below are empty cells where there will be a total for that month’s purchases. I want a conditional format formula to automatically bold and highlight the current month’s total and month name. Also, when I enter February totals next month and that number is input into February’s total, I want that month and total to bold and highlight BUT I also want the previous month’s bold and highlight to vanish at the same time. Is this possible?
View Replies!
View Related
Take Individual Dates And Total Into Summary Page Automatically
I have multiple tabs for each month (Jan, Feb, etc) for the 2008 year and I have a Summary tab. For the individual months, I have each calendar day in a column and whether the day was for sick, vacation, etc in the rows. So if someone was sick in April, I would mark the day they were out of the office. And so on... Well now I need to figure out how I can take those individual dates and total them up in the summary page automatically.
View Replies!
View Related
Tracking Daily Total Sales And Individual Tender With Data Extracted From .dbf File.
I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated. Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details. I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly? Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file? attached is book.xls and sales summary.xls for reference.
View Replies!
View Related
Formula- To Pull Cell Values Similar To A SUMIF Function (SUMIF(range,criteria,sum_range))
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
View Replies!
View Related
Function To Fill All Days Of Month To End Of Month Based On Workdays
I would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them. =IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.
View Replies!
View Related
Function To Fill All Days Of Month To End Of Month
function in a spreadsheet that will list all of the days in a given month automaticaly with the entry of the 1st of the month only. Ex; 10/01/05 entered dated 10/02/05 auto fill 10/03/05 " . " . " 10/31/05 end of auto fill I would like the function to stop filling dates at end of the month even for shorted months such as Feb.
View Replies!
View Related
SUMIF For A Specific Month
i thought i had something sorted out for this yesterday, but coming back to it today it hasn't worked at all. i've created a spreadsheet with records of what jobs my heavy plant repair company has opened, which contains such data as when the job was if and when the job was closed and the value of the job when it was closed. now i have to create a summary sheet that totals the values of the closed jobs in a given month. i've included an mock up of the database in question, but i just can't seem to get it to work.
View Replies!
View Related
Insert Total Formula Using SUMIF And Named Range
I am trying to put a SUMIF formula at the bottom of the data. Doing it manually is fine, but I want some vba to automate it, and my vba doesnt work. Sub balance() 'adds journal line so journal balances in each month Dim Rga As Range Dim Rgb As Range Dim Rgc As Range Dim Rgd As Range Dim Rge As Range Dim Rgf As Range This code just inserts text into the formula, rather than the range of cells.
View Replies!
View Related
Cumulative Total By Month
I have a list of daily sales which I would like to have a cumulative total by month. Once the next month is encountered, the cumulative total will reset for that month again. E.g. (please see attached file) Column C is my cumulative total. The cumulative sum will reset when the month changes from Jan to Feb. I can achieve the results using VBA but I need to distribute my report to parties whose VBA environment is disabled. So, I need to work around this with a formula.
View Replies!
View Related
Total Numbers By Month
I need to total how many '1's or '2's in column A by each month ( Date Format 01.04.07) in column B. Have tried to use the following formula to count all the '1's in May, but I cannot get it to work. =SUMPRODUCT(MONTH(A1:A100)=05)*(B1:B100=1))
View Replies!
View Related
Summing Total Per Customer For Each Month
Please see the attached example. I am working with Office 97 and I know that if I had Office 2007 this question could be easily answered with the "SUMIFS" function. But since Office 97 doesn't have that formula I need to find another way to accomplish what i am trying to do. In the gray boxes I need a formula that will sum the amounts for each customer for each month. For example, the total amount that shipped to customer A in October is 180.4. I will be applying this to a much larger amount of data, so please do not provide a formula that only works for the data supplied in this example. I will be continuously adding more info to my list over time and I want the count to automaticaly include the data when I enter it.
View Replies!
View Related
Total Days In The Month Based On Date
is there a formula that will say how many days there are in a month ? B9 contains the a refrence to the first day of the month eg 1/7/2008 I want the cell above it (B8) to return 31. If the date reference is 1/6/2008 I want (B8) to return 30. I'm using it to pro rata by the number of days in a month.
View Replies!
View Related
Count Each Month (Using Countif To Total Employess)
i have a list of employess who i need to count each month. i need a way i can say count if >0 for month = Oct. each employee has a summary of there work for a month in a list going down. example ___John Jan__0 Feb__19 Mar__50 ___Bob Jan__4 Feb__56 Mar__12 Right now i have a way i am summing up the total work for all the employees "=SUMPRODUCT(B21:B512,--(A21:A512=A4))" <------'A4=Jan in the formula' How could i change this to reflect count the employees and not sum the total? The idea is a need to know how many employees had a number greater then 0 for the month of Oct.
View Replies!
View Related
Calculate Total
I need a formula that will calculate the total of the items in column "W" when the row directly below it contains a "1" in column "V". It will need to calculate the totals from that row all the way up to the next row that contains a "1" in column V. Example: In X14 (where I will put the formula) it would total W9 through W14. However the formula still needs to be able to calculate the total if there are more or less cells to sum. So that the same formula could calculate that in X8 it will total W5 through W8.
View Replies!
View Related
Trying To Add Total Hours Worked In Current Month
I'm trying to figure out what is wrong with this formula. =(SUMIF(Q14:Q4995, "<="&EOMONTH(TODAY(),0),W14:W4995))-(SUMIF(Q14:Q4995, "<="&EOMONTH(TODAY(),-1)+1,W14:W4995)). I've got a cell that adds Total hours worked which pulls from the same column of entered data as the formula above and that cell works. My hours for the month however just shows up as zero. If I try and edit the formula or even just highlight it to copy it and then tab out of the cell this shows up... 1/0/00 If I undo the highlight and tab it will go back to showing zero. I've checked my dates that I entered and they are correct. I'm at a loss as to how to fix this formula.
View Replies!
View Related
Calculate The Total Revenue
I have a table which shows the monthly revenue of Company ABC , by client / by location / by business line. ClientCountryBusiness LineJan-10 Feb-10Mar-10A001SingaporeResidential$ 50,000.00 $ 23,333.00 $ 54,115.00 D003SingaporeRetail$ 50,000.00 $ 21,548.00 $ 54,654.00A001SingaporeCommercial$ 63,321.00 $ 75,542.00 $ 21,564.00 D003AustraliaResidential$ 26,564.00 $ 50,000.00 $ 45,654.00 G002AustraliaRetail$ 50,000.00 $ 21,546.00$ 63,321.00G002AustraliaCommercial$ 26,602.00 $ 65,341.00 $ 24,568.00 A001Hong KongResidential$ 33,565.00 $ 26,564.00 $ 64,454.00 G002Hong KongRetail$ 54,232.00 $ 50,000.00 $ 15,454.00 D003Hong KongCommercial$ 26,564.00 $ 21,564.00 $ 23,333.00 If I want to calculate the total revenue for the month of January based on the following variable criteria: Criteria Client: A001 Country: Singapore Month: Jan-10 I can achieve that with the use of SUMPRODUCT. However next month, I will then have to manually change the SUMPRODUCT formula so that it will extract data from the Feb column instead of the Jan column. Is there a formula which will not require me to change the formula every mth? Ie. I can get my answer simply by changing the criteria?
View Replies!
View Related
Calculate Total Entries
I have a list that is numbered 1 2 3...ect. I want to have a cell that tells me the total number of entries. I have a I'm not sure how I could do this. 1 2 3 4 5 6 1 2 3 Total = ? If I do a normal sum it would just give me all of those numbers added up. I want the total to read 9 (in this case) My list changes all the time so I want that total to tell me how many entries I have.
View Replies!
View Related
SUMIF To Calculate Daily Gains/losses
I currently have a profit/loss spreadsheet where I use a SUMIF to calculate daily gains/losses. =SUMIF('Daily Sheet'!B:B,B377,'Daily Sheet'!H:H) Basically, on the 'Daily Sheet' page when a sale is made I write it in a row. Each sale gets its own row. One of the column's has the date. (Column B) Column H has the respective gain or loss for that sale/purchase. B377 in this case contains the date 18-Nov. (B360 to B389 has Nov 1 through Nov 30) So this cell will calculate all the gains and losses in column H for each sale/purchase entry I have for that day. Some days I have 30 rows, others only 2 or 3, so this works great. My problem now is that my rows have gotten much more complicated and detailed, and now I'd like to be able to only sum column H:H if it meets TWO criteria. (I'd like to add in Column C that specifies the type of sale or purchase being made, which is R, T or S for my purposes.) So I'd like to somehow write a sumif that only adds up the sales for 18-Nov AND sale type R.
View Replies!
View Related
How To Calculate A Total Dependant On Variables
In the file I have attached, I need to work out how to give the final total (cell G27) inclusive of VAT, dependent on the value assigned in cells K17-K26, which are explained in the list starting cell B29. At the moment the value given is net of VAT, and I need the total figure in G27 to recognise that the first item needs to be at 15%, the second at 5% and the 3rd at 0%.
View Replies!
View Related
Calculate The Total Weight Of Any Letter
I cut alphabet letters out of MDF. I need to calculate the total weight of any letter. I have a program that will give me the area of any shape. In the picture below I have started with the letter “A”. B7 is the over all area. D7 is the small triangle. G7 is the total area minus the triangle D7. H7 is the thickness and I7 is the total volume. The weight of my MDF I have set at is 850kg cubic meter. That’s set in A3. I need the total weight of the letter in J7 to be in grams. The idea is, I put in the areas and set the thickness and it gives me the total weight.
View Replies!
View Related
Cell To Calculate A Running Total
I'm trying to have a cell (eg.A1) where i can input a dollar value and when enter is pressed, on another cell (eg.A2) the dollar value gets added, then the original cell (A1) is cleared for the next input. Then when I put the next input (A1) I want it to add on to this running dollar total (A2) and yet again A1 is cleared for the next input. In addition to this I require an additional cell (eg.A3) that when each time a value is added in A1 it counts the times this is done.
View Replies!
View Related
Calculate Matches, Total Results
In the attached sheet, I need to find all items in column D that start with "LAB". For those items, Multiply the corresponding values from column B x column E and return the sum of all to cell T1. Once the total is in cell T1, I need to then delete the values in column E for those items (or change them to zero). I dont even know where to start with this.
View Replies!
View Related
Calculate Percentage Of Total Salary
I have percentages set up for my monthly costs (student loans, food, spending money) and I want to be able to type in my weekly salary and have the percentages break it down into dollar amounts. Later, I'd like to put that into a pie chart, but I'm really only asking for help referring each category/percentage to the total $ value I enter each day/week/month.
View Replies!
View Related
Calculate Column Total Via Vba
I've got an excel spreadsheet that is using imported figures from another source. I'm just trying to create a macro that will locate the last cell in Column N, Then will total all the cells above it. I've got the below code to select the last cell in Column N. myrow = Range("N1:N" & Range("N65536").End(xlUp).Row).Count For i = 2 To myrow Range("N" & i).Select But I can't figure out how to do the sum part. I've tried to record a macro but to no avail. Since the number of rows will change each day, I need to somehow catch the range. This is part of a bigger project, but if I can get this part working, I think I'll be home free. Attached is a small example.
View Replies!
View Related
Using SUMIF To Calculate SUM Over 30, 90, 365 Day Period
I have a logbook which already uses a number of SUMIF formulas but have been trying to create one so that totals the number of hours flown in the above mentioned periods. I am attempting to copy the formula and then modify for the relevant column in this case: =SUM(IF(FlightDates<(TODAY()-90), 0, FlightLength)) FlightDates is the named range of the calender date in column A and FlightLength is the named range for total flying in column Z. However even though this forumla works in another situation e.g. tracking number of landings in the last 90 days, it comes up with an error.
View Replies!
View Related
Calculate Taxes And Pension As A Percentage Of Total W-2
I have attached a sample workbook of what I am struggling with. It is for payroll, and I need to calculate taxes and pension as a percentage of Total W-2. The problem is that the 'Total W-2' column then deducts these taxes and pension. Currently, the worksheet has about 30 columns out to the right using 'guesses' to back into these numbers, but I hope there is an easier way.
View Replies!
View Related
Calculate Colum Total Based On Formula
I would like to have something that looks like a running total, but isn't. I have in a colomn 'rate' and after that a column for each month in a year. I would like to have at the bottom a total cost, so rate x hours. have a look at the picture. is there a formula for this? ratemayjunejuli $10 2,02,01,0 $12 2,03,00,0 $10 3,02,05,0 $15 4,02,53,0 total cost $134,0 $113,5 $105
View Replies!
View Related
Format To Calculate Total Hours And Minutes
Having trouble adding a column of minutes and converting the total into hours and minutes. Say Cell A1 through Cell A18 each have 12 minutes in each cell. I want cell A19 to tell me how many hours and minutes of total time that have elapsed. I have tried hh:mm, [hh]:mm, but nothing works.
View Replies!
View Related
Calculate Total Time With A Maximum Per Day
Date Start End Total 12/8 - 2:00 3:00 = 1 12/8 - 4:00 5:00 = 2 12/8 - 20:00 21:00 = 5 12/9 - 23:00 0:00 = 3 12/9 - 3:00 4:00 = 4 =SUMIF($A:$A,">="&TODAY()-1,D:D)/24-SUMIF($A:$A,">"&TODAY(),D:D)/24 In the example above, E2 calculates the totals between 12/8 and 12/9 giving me 15:00. What I want the formula to exract is the total from Col D the total from 12/9 to the previous 24hrs. 12/8 has 2 entries, and 12/9 has 3 entries. The total I want to extract in 24 hours, NOT 1 day, is 14. What formula can I use that will calculate the totals in 24 hrs.
View Replies!
View Related
Calculate Quantities Based On Set Total
I have three values. The Quantity of items, the cost per items and the cost for the entire order. In some instances I want to give my clients items for free so I need to lower the cost per item in order to keep the entire order cost the same. In order to ensure we are on the same page let's use the following associations: Y = Quantity X = Cost Per Item Z = Order Total So essentially I need a formula that will automatically calculate the cost per item (X) when more Quantity (Y) is added in order to keep the order total (Z) the same.
View Replies!
View Related
Calculate Interest Per Month
Sample data: 02-06-09 to 18-06-09 earned $2500 19-06-09 to 23-08-09 earned $3500 24-08-09 to 31-12-09 earned $4500 I am thinking if there's a formula that can calculate the interest earned for each month from Jun-09 to Dec-09.
View Replies!
View Related
Calculate Fiscal Month
I'm looking for a way to calculate (name) a fiscal month based on a date range. Example attached. Columns A & B are a date range and column D is the desired outcome. I'd prefer a formula solution, but am open to anything as I haven't a clue how to begin with this.
View Replies!
View Related
Calculate Total Time Worked. Decimal Times
I need to create a formula to calculate monthly hours worked (144.20) by hourly rate (£14.25). So far everything ive tried has given me awrong answer. I cant seem to make the total right. From what ive come up with (Not good) the total accepts the hours-just not the minutes.
View Replies!
View Related
Auto-calculate Last Day For A Month
I'm working on a Calendar. One where all the user does is input the year, and the rest of the Calendar fills itself out, as to the days. Leap year is causing a small problem. There may be an easier way to do this (actually, I'm sure there is, but anyway), is there a way for a cell to automatically figure the last day of the month? IE: I put "2007" in a field. Another cell auto matically reads as "28" (last day of Feb for this year). Subsequently, when I enter "2008", the same field reads "29". The rest I think I got ok, but everytime I get a leap year, it shoves all my formulas down a cell, thanks to the extra day, and they're all off by one (February calendar showing last day as "01" from March, and "29" as the first day for March, from Feb).
View Replies!
View Related
|
TRACKER
