Summing Data From If Results
Oct 5, 2013
How to sum numbers from multiple columns that match certain criteria.
My spreadsheet is a funeral team tracker and it's only the 1st day of the month, Each corresponding day will eventually have it's own tab once I nail down this first one.
Basically the page is laid out into 6 different details a team member can do. A team member gets their hours from the "Total Detail Time," that gets calculated from the show time til the return time. The team who worked the detail gets added to the bottom of the Detail they worked. I am just trying to total the "total detail time" each time a member works it. So, a member could work say Detail 1 and Detail 3, so I would just want to sum the total detail time from those two days where the member is shown.
I was trying to use =SUM(IF((Detail1,N13,B10)+(Detail2,N13,D10)+...
Note: I have range names for each bottom portion of the details to shorten the formula (i.e., Detail1, Detail2, etc.)
[URL] ...........
View 5 Replies
ADVERTISEMENT
Oct 24, 2011
Is there a way to SUM the result of the followig for B4:J4 and B16:J16, the other cell references remain static:
=(B28*(P28^2-IF(ISNUMBER(B16),ABS(B4-B16)^2,B31)))
I a currently using the following which is rather lengthy and not efficient to maintain/edit:
=(B28*(P28^2-ABS(B4-B16)^2))+(B28*(P28^2-ABS(C4-C16)^2))+
(B28*(P28^2-ABS(D4-D16)^2))+(B28*(P28^2-ABS(E4-E16)^2))+
(B28*(P28^2-ABS(F4-F16)^2))+(B28*(P28^2-ABS(G4-G16)^2))+
(B28*(P28^2-ABS(H4-H16)^2))+(B28*(P28^2-ABS(I4-I16)^2))+
(B28*(P28^2-ABS(J4-J16)^2))
View 2 Replies
View Related
Feb 8, 2007
A friend of mine asked if there is a formula that will add the "Y"'s and "N"'s based off of a filter per certain dates. Below is a brief example.
2/18/2009Y
8/15/2006N
2/18/2009Y
8/15/2006N
4/6/2005 N
4/6/2005 N
We want to be able to sort by dates so regardless of which date we pick, below the Y's and N's will be the total for the Y's and N's that are visible. If sorted by April, it will show 2 N's and 0 Y's, and so on. Is there any formula out there that can do this.
View 3 Replies
View Related
Jul 25, 2012
My task is to sum up data in a worksheet which counts on the number of "working days"(Exclude Sat, Sun and Public Holiday) between "the date of receiving the case" and "the date of completion" of the same case.
So here I have already listed out the days of each month to distinguish "Working Days" and "Non-working days" by "1" and "0" respectively for the easy calculation of the no. of working days as I think.
like this(January):
Date
Results (Exclude holidays)
1/1/12
[Code].....
Then, I use the lookup formula to determine the receiving date (beginning) and the submission day (Ending) sucessfully.
Receiving date
completion date
Receiving date
completion date
1/3/2012
1/8/2012
1
0
Here comes the problem, I want to sum up the range of cell from the result of 1/3/2012 to 1/8/2012, i.e. (1+1+1+1+0+0)=4 to get the total no. of processing days of each case. I tried to use INDIRECT like "=SUM(INDIRECT(VLOOKUP(F5, A2:A226, B2:B226)):INDIRECT(VLOOKUP(G5, A2:A226, B2:B226)))" but it returns me #REF!
View 3 Replies
View Related
Jun 6, 2009
I am trying to do is when a user enters in a GL (a 4 digit number) from the list (tab 2) I want it to appear in the summary box below. However the same GL number can be used multiple times so I will have duplicates. The data entered above will always change there is over 200 possiblities the user can enter. So I don't want the data to be specific it will populate based on what is entered by the user.
Can this even be done?????
Say if the user entered:
Column C Column G
4606 $20.00
4606 $20.00
2134 $15.00
2301 $35.00
4606 $100.00
The data in the GL and currency column in the summary box would appear as the following:
Column C Column G
2134 $15.00
2301 $35.00
4606 $140.00
View 3 Replies
View Related
Aug 22, 2014
I'm pretty new to Excel and trying to create a spreadsheet for a friend. For the part that I'm working on right now, I have a cell to track donations received for every day of the month for every month of the year. Donations can be split into three categories (Field trips, Speakers, and Misc), so next to the cell for the manually entered $ amount I have another cell that provides a drop down menu to select from these three categories.
What I would like to do is have three cells at the bottom of the page that will total the $ amounts for each category. I've attached the page that I'm referring to.
Attached File : Example.xlsx‎
View 3 Replies
View Related
Oct 27, 2009
The formula is in C4, and I am trying to sum the data when the value of B4 is matched in the table. see attached.
View 4 Replies
View Related
Jan 21, 2010
I’m trying to get the total number of participants in two different sets, with selection criteria attached to both (I’ve attached an excel file, as the example with those scoring +ve highlighted in yellow). The first set A (b1:b15) I want to select all values above 5, and the second B (c1:c15) set above 10. There other provisor, is that the A and B are further split into two groups (one above and one below 20).
In summary, I need to count all the A>5, B>10 (And both have to be above 20 in column D), but not to double count the ones that have Both A>5 and B>10. I don’t want any duplicates to be counted, so summing the total of A and adding it to B won’t work. In essence it’s an attempt to use the A Union B function that you’d observe when using venn diagrams.
Is there a simple way/formula of doing this?
View 13 Replies
View Related
Jan 15, 2007
How do I edit the VBA so that the 1st data cell is selected, and then the program calculates the sum of the data values, and then stop summing when I reach end of data?
View 5 Replies
View Related
Jun 1, 2009
I want to sum certain data, which meets two conditions.
My data set contains three columns and a lot of rows. The columns are the following:
PostalCodeDeparture
PostalCodeArrival
PassengersInCar
I want to sum the total number of passengers with departure postal code 5100 and arrival postal code 5110. (and I want to do the same for all other postal code combinations in the data set)
With "SUMIF" I can only include one condition.
View 4 Replies
View Related
Nov 23, 2007
I have a set of data maintained in a number of tables within an Excel Worksheet.
All of the tables are the same layout and occupy the same Columns.
I want to summarise data from certains Rows within these tables at the bottom of the Worksheet.
Is there a better solution than simply doing something like?:
=A1+A11+A21+A31+A41
View 9 Replies
View Related
Jul 18, 2006
I have a lot of paperwork that needs to be entered into Excel. When I finsih entering the data, there will be many workbooks containing many different worksheets. I know how to summarize each workbooks but is there a way I can add specific columns in each of those summary sheets into a new workbook? I guess the question is can I sum data from different workbooks into a new workbook?
View 5 Replies
View Related
May 25, 2007
See attachment. For example, imagine data in cells A2:N25000 (obviously, this example in the attachment has been shortened). At this point, also imagine that rows 7 & 8 did not exist. So, there is a continuous stream of data for vaious counties in A2:N25000. For the purpose of context, the data contain mortgage information for all the lenders in a particular county for an entire US state.
Is it possible to create a macro that would insert two rows after each county in the data range (e.g., rows 7 & 8 in the attachment)? In other words, in the first row a macro would insert a row (row 7) that sums the results of the top 25 lenders in each county. Then, the macro would insert a second row (row 8) that sums the results of ALL the lenders in each county?
View 2 Replies
View Related
Apr 26, 2008
I have a set of data that I would like to break into groups, but I do not know what the groups are. I would like Excel to help me find the groups.
More specifically, my tax data consists of the following columns (I'm simplifying): parcel number, dollar value, tax amount, days late paid.
123435, $12000, $100, 20
234234, $23000, $230, 05
etc.
Of course my Excel "results" would omit the parcel numbers, but it would propose groups (and how many parcels in each group) such as: ...
View 6 Replies
View Related
Jan 14, 2014
I have a summary tab in which I am trying to sum data based on specific variables from 30 other tabs in the worksheet
- In the summary tab, I have months (one year worth) over the top row and consultant names in the first column
- Each other tab represents a project
- I want for consultant 1 in summary tab, to have:
For january, the sum of dollars spent in january in each project (so across all tabs)
Same for february to december.
I have attached an exemple Note that the consultant names will always have the same syntax but will not necessarily be in the same cell in each tab.
View 5 Replies
View Related
Feb 10, 2014
I'm looking for a formula that would look up the individual employee (i.e. Name1 for example) in the January sheet and then look for and sum any amounts that have a number and a symbol as reflected in A2 through A10 (8V, 4Vâ–²,etc), which would then be calculated with a similar formula for the February sheet, specifically the time period between 1/26/14 and 02/08/14 as indicated above.
As employees can switch shifts, the formula would have to locate now in February where employee (Name1) is and find that row for the same symbol/number calculations for this same time period in that given month. Currently I'm using an array formula which can be seen in the attached file in cell I4. The problem is that the formula assumes that Name1 is always in the same spot in all monthly sheets which isn't the case month to month. The formula looks like this without the vlookup portion:
[Code] .....
I also need to do a similar vlookup or match, etc with this formula too:
[Code] .....
Attached File : sample payroll.xlsm‎
View 7 Replies
View Related
Jun 18, 2009
I shall use a basic example to illustrate my problem - see attached - as the spreadsheet im actually working on is huge and contains sensitive data. So, In the spreadsheet attached, the problem is: I am mowing the grass of a football pitch and getting paid by the m3 of grass i mow. however i'm getting paid more when i mow at greater distances - defined by certain 'reaches'. so i need to know how many m3 ive mowed in each reach. I make a note of where I start on the pitch and where i end, and also how much grass i mow.
so im looking for a solution that is intelligent enough to distinguish where Ive taken grass from and how much, given that i know where i started, where i finish and how much i take.
View 5 Replies
View Related
Jan 16, 2012
formula that will find "John Doe" through sheets 2-10 and check the Win column for a "w"..if a W exists, Sheet 1 will calculate numericaly how many times it shows progressively, but only for his name and not others.
Sheet 1 B7=John Doe
C7 = total W's though sheets 1-10 for John Doe
Sheet 1
B7 C7
John Doe total W's
[Code]...
This is a alteration to an existing question I asked 2 Days ago, I redesigned a new spread sheet to start over with a new approach to it.
View 2 Replies
View Related
Feb 16, 2007
I have the following sheet with reg hours and ot hours. I need to insert a column that sums these two items. I used the macro recorder and it only works when the same amount of ID's or less. If there are more ID's it does not sum the remaining ID's. What it currently looks like:.........
View 3 Replies
View Related
Jul 4, 2008
I have circa 500 sheets (all with identical layout) in the same workbook. All the data is displayed in Columns "A" and "B". The entries in Column A are variable (5-6 digit numbers) B contains numbers between 1 and 200.
I would like to be able to add together the sum of the numbers in B against an entry in A- I have a list of all the entries in column A.
The data in column A is in ascending order but may not always be in the same cell reference.
E.g. "123456" and appears in 3 sheets
(sheet2) A9 ="123456", B9= 5
(sheet 234) A111="123456", B111= 7
(sheet 456) A87 ="123456", B87=3
"123456", total 15
View 9 Replies
View Related
Sep 22, 2009
I have a table of data: - In cell A1: to C7
A B C#Group Aug-091Merchant Bar2002Plate3Structural - HR1004RHS5Plate3006Structural - HR300
What I would like is a formula that sums up column C if Column B of that row is "Plate" for example
View 12 Replies
View Related
Jun 3, 2006
I have one last question. is there a way to make this a little more complicated? for every two possible combinations of names, i have a value. is it possible to create a fourth colum, in which the sum from the three values is calculated?
for example-
12 different letter names (a-l)
after running the code derk sent, i now have a-l in cells A1:A12
I also have every combination of 3 using these 12 names in columns C D and E
take for instance the combination of three names (a, b, c). i have values for ab, ac, and bc in columns G, H and R respectively. can these values be summed together and averaged in the fourth column?
View 4 Replies
View Related
Apr 1, 2007
How do you sum data from several cells that derive there answer from a lookup formula that is base on a selection from a drop down menu in another cell. The 6 drop down cells have the same ten items to choose from. Once those items have been chosen I am trying to summarize those six cells to ge a total for each of the item chosen.
View 9 Replies
View Related
May 3, 2007
I have a spreadsheet which links to an external source, runs a sql msquery and retrieves data based on dates selected by the user from two drop down lists. From Date and Date To.This works fine. However I also need to total any fixed data which resides in the same spreadsheet based upon the same dates selected. The end user selects 2 dates , say 15/04/2007 (this relates to a week number,week15)and 28/04/2007(week 17)
A column of data lists the week numbers (in cells A3 - A22) and next to this their respective production quantities in cells B3-B22.
I now have a problem in totaling the production quantities in the worksheet as my user is not just selecting the week numbers 15 and 17 but 15,16 and 17.
qty
week 15100
week 16123
week 1789
How do I sum from 15 to week 17 inclusive or any other range selected?
View 7 Replies
View Related
Feb 28, 2013
I have a table with a number of columns, all of them number format, first one is a unique ID tying all my tables together.
The table contains a running tally of credits for each member and I need import reports every now and then adding on to the tally.
How do I set this up so that I can have Access read an Excel file with data to be added to the existing (not replacing, adding up) and a new record created if it doesn't already exist?
I've tried the append external data, but that fails as the ID field already exist most of the time, even when I created a blank copy of the table I get error messages even if it then actually imports (first time).
Do I need to format the Excel sheet in any particular way to match the tables number format? Is there additional steps I need to take? Would this work even if the import sheet included the same user several times (each occurrence to be added on).
I really don't want to have to step through every user and manually add up and update each credit.
View 2 Replies
View Related
Jul 1, 2013
Basically, we have 2 tablets tallying data and writing to the same .csv file. That csv file is then copied into a front page of a much larger excel worksheet where the tallied data is sorted and used for a variety of reasons. This works perfectly using index and match when only 1 tablet is being used, however, when both tablets are writing to the same csv at the same time it creates 2 lines instead of 1 line.
For example 1 tablet might look like this:
Lot 45689 4 6 8 9 1 5
2 tablets looks like this:
Lot 45689 2 3 1 8 1 4
Lot 45689 2 3 7 1 0 1
for the tallies, i would imagine a =SUMIF function might suffice, but that doesn't solve my problem of there being 2 lines with the same lot #.
I am only dealing with this issue from the excel side, and have no control over the tablet functionality or the CSV file which further limits me.
Wanted to add that the Lot #s change daily and often so with my understanding of pivot tables this rules them out as a viable option.
View 10 Replies
View Related
Mar 6, 2013
I have been trying multiple SUMIFS and SUMPRODUCT formulas to try and revamp my budget spreadsheet. My first criteria is the month being a certain number (contained in a column), if that condition is met it needs to look for the account number I am budgeting for (row heading) and if it matches both criteria I need it to sum the Column that has the row heading of the account number. I have succeeded in doing a SUMIFS and manually finding the column that has the heading of the account number I am looking for, but I would like this to be an automated process so I can copy the formula. Below is the data I'm using (a very, very small portion of it), and the results are how my budget sheet is going to look. These will be on separate tabs in the workbook.
EX: For July electric revenue (44211) I need my formula to: (if the Month column=7 & if the Account Code row heading=44211, sum the column that has the heading of 44211). So far I cannot get this to work.
DATA
Account Code
44211
44215
[Code]....
View 4 Replies
View Related
Apr 17, 2007
I'm wondering if it's possible to do the following using Pivot tables. I have month period from 1-1-2006 until today by month. Is it possible to have in the pivot table a field that shows the total of 2006 and then another field that shows the total of 2007?
View 4 Replies
View Related
Feb 26, 2008
I have a worksheet that contains codes in column A and values in column C
The last value in in cell C50. I want to add all the values in column Code, but exclude any value where the codes in column C contains 50, 51, 55.
for eg if the code is 5000, 5001, 5100, 5105, 5500, 5501, 5510 etc the value must be excluded.
View 9 Replies
View Related
May 14, 2013
I have a table that looks like the following, only it's actually much larger:
_Red Blue Green Blue
A 2 4 2 3
B 5 2 1 1
C 3 1 2 5
D 2 3 4 2
As an example, I'm trying to sum all cells that match Blue and C. The answer should be 6, but I always end up with either zero or #VALUE.
View 4 Replies
View Related
