Summing Based On Criteria
Jul 20, 2006
I am currently trying to reconfigure a dataset into something more managable for our regression needs. The issue I am having is trying to automate as much of the data transfer as possible. The data set is set up as follows (for example):
Yr Tag Value
0 2 #
1 2 #
2 3 #
3 3 #
0 1 #
1 1 #
2 1 #
3 1 #
etc. where tag is a special code we have developed based on the number 1, 2, 3, and 4
What I would like to do is develop a summing formula so in year 0 I can sum all the values if the tag is equal to 1 and then I would do that for the numbers 1 through 4 in my "compilation" worksheet.
View 17 Replies
ADVERTISEMENT
Apr 30, 2012
I'm making a spreadsheet that tracks instances when something goes above or below a threshold. Anytime it goes below, a column populates with the number 1, and if it happens to go below a threshold on a Friday, a separate column populates with the number 2. If its doesnt go below, the column with 1 does not populate, however, every Friday populates with a two.
I need a column that adds the 1 and the 2 when the threshold is broken on a Friday, but only shows the 1 when the threshold is broken on a non friday and shows 0 when the threshold isn't broken, but happens to be a Friday.
View 1 Replies
View Related
Aug 10, 2006
I am not sure my title is accurate so I will explain what I am trying to do. I would like to total the numbers from each row which has "Monthly Totals" in column A. There are currently only two of these but there will be more added over the year. Rather then have to edit the formula to include the new cell I want added to the total, is there a way to create a formula which will automatically scan Column A for "Monthly Total" then add the cell from that row to the running total at the top? Basically, If any cell in Column A has "Monthly Total" add the cell from column B (from the same row) to the running total in cell B1. This way I could add as many "Monthly Totals" as needed and I wouldn't have to edit the formula in cell B1.
View 7 Replies
View Related
Mar 31, 2014
I am looking sum formula to display my data with 3 criteria (display by this month, until this month & until last month) based on header column/correspondents, then in cell L3 as selected month display..
For further information, check workbook attached...
SUM 3 MODELS MONTH.xlsx
View 7 Replies
View Related
Jul 7, 2009
I have a cell that I need to make a calculation if certain other cells =Y, or to show "0" if the other cells =N. I can do the calculation for the Y part but how do I add the N part of it? Here is what I have:
=SUMIF(J19:J40,"Y",H19:H40)*0.08
View 13 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 10, 2009
Imagine column A sorted by invoice numbers. Column B has payments applied but due to movement between unapplied cash, some numbers appear as negatives and positives for the same invoice number.
I want to have a formula which says that for each invoice number, look in column b and eliminate opposite numbers and post only unique values. Keep in mind that there may be multiple lines with the same invoice number.
For example, starting with line 5, for invoice 123, payments 400 and -400 in line 5 and 17 and payments 315 and -315 in lines 7 and 9 will zero out but any other unique payment will be picked up and displayed in column C. Lines 5 and/or 17 will be blank in column C.
View 9 Replies
View Related
Feb 23, 2012
Excel 2007. I have an Excel file that contains a data dump from an external database file with numerous analytical sheets that perform calculations. Some of these calculations utilize the SUMIFs function that was introduced in Excel 2007. This function does an outstanding job of summing a column on the data sheet based on multiple criteria.
However, someone high up in management in my organization would like to "drill down" into the data behind the
SUMIFs formulas to get a quick snapshot of the lines in the database that roll into the
SUMIFs formula. =SUMIFS(DataBase!E:E,Data!A:A,C7,DataBase!B:B,D7,DataBase!C:C,E7,DataBase!D:D,F7)
If I double click on a cell with the formula above, Excel takes me to the Database tab and selects Column E which is close, but not exactly what I need. What I really need is for Excel to only show the rows on the database sheet that make up the total in the SUMIFs formula and not the entire data dump from the database.
At present, we have to manually apply the autofilter on multiple columns to show the rows in column E that make up the total in the SUMIFs formula which is a tedious and time consuming task. Is there a way to force Excel to do this? Suggested custom database application or pivot tables, but we do not want to reinvent the wheel.
View 2 Replies
View Related
May 14, 2014
I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.
Sample:
Location A Class:AA Segment: Sports Bar Rep: John Smith
Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1
I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.
I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.
View 2 Replies
View Related
Sep 9, 2009
[data] ...
above is a sample of a very large table that I need to extract year to date/month to date information and year over year comparisons- the top line is the Sales territory (eg 112) down the side I have a code that will code each month with each year - eg(108 - which represents JAN08) I need a formula that will read 108 and 112 and calculate everything that pertains to that criteria. So for this instance territory 112, Jan08 would be a total of $8,613 - can anyone help? Oh, and the columns are not fixed as the next download might have a different order of territories across the top.....
View 9 Replies
View Related
Jun 16, 2009
I would need to get this next thing working:
H I J K L M N O P
Type No. No.Points Points/RN Points/KR Points/KV Points/ER R/V Total
5 KV 12 4 2 3 4 6 0 8
6 RN 90 18 3 4 5 8 0 26
7 KV 12 4 4 4 5 6 10 13
8 ER
9 KV
10 KR
...and so on, with the following logic
If Type = KV, then P$ = sum(J$,M$,O$), but if it is something else, like RN, then it needs to sum J$,K$,O$
I've tried using the following:
=IF(ISNUMBER(SEARCH("KV";H2));SUM(J2+M2+O2);0;IF(ISNUMBER(SEARCH("ER";H2));SUM(J2+N2+O2));
but only the two first ifs work, which is kind of logical, since the second if is the "value if false" statement. Is there any way to get this to work?
View 9 Replies
View Related
Aug 27, 2009
Column A: Start Date
Column B: End Date
Column C: Ethnicity Code (letter and number such as B1)
Column D: Hours
What I need to do is work out how many hours each ethnic group did in a specific month.
View 14 Replies
View Related
Jan 22, 2010
I would like to sum G column (in my sample) that will meet a date criteria in C (like from 10/9/2009 to 11/9/2009) or similar. I tried it already with a formula =IF(c2=DATEVALUE("10/9/2009")=<("11/9/2009"),G2,0) could not make it work but its okay without the =<, or a single entry date (a sample in AB column), with this formula its being transferred to another column and being summed, what am trying to figure out is to make a formula or function that it can be incorporated in a sum at the bottom of G column if possible, I would be able to check how much cash collected in a week, days or month....
View 4 Replies
View Related
Jun 21, 2007
I am trying to SUM a range of cells R9:R39, but I only want the cells added that are greater than 0 (zero) and also I don't want the Sub totals included in that sum.
Column R
-507,784
-507,784 Sub
-25,292
-28,700
32,461
-26,632
158,586
0...............................
View 9 Replies
View Related
Apr 23, 2009
I'm trying to sum a criteria of all M's in one column that are x's in a different column, throughout multiple worksheets.
I'm able to get the summary number for 1 worksheet using the below formula (*W1 is the worksheet name); however, how do i encapsulate all the worksheets (lets say W1 through W10), please note that some of the worksheets have different ranges (meaning, not all are from Row 2 to 6)
=SUMPRODUCT(--(INDIRECT("'W1'!C2:C6")="M"),--(INDIRECT("'W2'!D2:D6")="x"))
I tried to replace W1 with W1:W10.
View 9 Replies
View Related
Jul 31, 2006
I am having an excel problem such that i have several months in a given year and I want to set up a formula where I can enter a specific year and it will return the sum of all the cells that belong to that year (see attached sheet). So basically if I have 2 months in 2006, 2 in 2007 and 2 in 2008 with corresponding values for each of those months, I want to enter a year in another part of the sheet and a value is returned to me to show me the sum of the revenue for all the months that are in that year. So if I enter 2007, it will sum both those values that belong to 2007.
The years and months are dynamic so I can't just sum the columns because in column D it could be 2006 (depending on the other inputs of my spreadsheet) or it could be 2007, so I essentially need some formula that checks the array to see if that cell belongs in a year that matches the criteria and if it does sum it to all the values that match that criteria. I have tried using vlookup, match, index but can't get it to work.
View 2 Replies
View Related
Nov 18, 2008
I am trying to review a cell range for a specific criteria, and then sum up another cell range if the criteria matches. Here are the formulas I have typed in - there are two columns I am trying to calculate using the same formula, they are next to each other:
=SUMIF('MASTER POINT SCHEDULE'!I2:I841,"0ACA101",'MASTER POINT SCHEDULE'!O2:O841)
=SUMIF('MASTER POINT SCHEDULE'!J2:J841,"0ACA101",'MASTER POINT SCHEDULE'!P2:P841)
View 9 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
Aug 5, 2008
I am in need of creating a User Defined Function in Excel that will sum numeric values based on two text criteria. I have a large set of data in an Excel worksheet that includes a column for " Market Type" and a column for "Location ID". I would like to develop a User Defined Function that allows a new or inexperienced user of the template to use the User Defined Function to select first a "Market Type" then select a "Location ID" and get the sum of the amount those values represent. A SUMPRODUCT function in Excel works for this, but can be awkward or intimidating for a new or inexperienced user. For that reason, I am specifically looking for a User Defined Function that will simply the formula for them while at the same time maintaining the degree of accuracy I need.
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
Dec 29, 2012
I have a sheet with ascending week-ending dates in the row at the top.
Next row is the man-hours worked for each week.
Next row is the # of First Aids for each week.
Not every week has a first aid and I want to sum the number of hours worked since (not including) the week of the the last First Aid.
There is a summary sheet which pulls the data from any selected week via a HLOOKUP function. So, I'm thinking I'll add a hidden row with the new "Hours since last FA" under the # of FAs row and insert the magical formula into each cell in that row.
The dilemma is I can't come up with a formula/function to calculate the "hours since" data.
View 6 Replies
View Related
Mar 21, 2014
I'm looking for a way to have a formula that adds to a number based on whether or not it's past a certain date.
So the starting number is in cell D1. A1:B4 contains dates and numbers to add to D on a certain date. In column F I want the formula to look at the date in column E and then make any additions to the number in D1 based on the date and the numbers from in columns A and B.
So as an example, 03/02/15 would be 90, since since we're adding 20 from 01/15/15 and 02/15/15.
A
B
C
D
E
F
G
1
01/15/15
20
50
12/28/14
[Code] ....
View 2 Replies
View Related
May 29, 2008
I have a chart with multiple bundles of rows....
Variable $500
Fixed $400
Step $500
Total $1400
Variable $500
Fixed $400
Step $500
Total $1400
Variable $500
Fixed $400
Step $500
Total $1400
Total Variable $________
Total Fixed $________
Total Step $________
Is there a formula that can help me sum up a total value for the Variable amounts? IE. Variable, Fixed, Step in column A; Dollar amounts in column B
Is there a formula that can recognize the word "Variable" in column A and then pull the number from the adjacent cell in column b?
Want to avoid having to Ctrl+click the hundreds of cells that I would have to in order to do a simple calc =sum(b1, b4, b7, b10) etc. if you know what i mean.
View 9 Replies
View Related
Feb 11, 2010
I have been trying to search an array and cannot find the correct combination of functions to get the information I want. I might need a macro to do this but I will try anything. Here is an example:
1 A 1
1 B 2
2 A 3
2 B 4
3 A 5
3 B 6
I want to search for 3 and A and return the value in the 3rd column. I have tried several functions but cant figure out how to make sure I get the correct value in the 2nd column that corresponds to the value in the 1st column.
View 14 Replies
View Related
Aug 30, 2012
I've got a table with columns for each day of the month, the second row contains the days of the week (MON, TUE, WED, etc...). I want to have two cells at the end of each line, one for summing the numbers in the working days (mon-fri) and another - for the sum of the numbers in the weekend cells.
The idea is the change the content of the second row every month so that the days of the week correspond with the respective date.
what formulas should I employ to accomplish this task and how exactly to do that?
View 6 Replies
View Related
Feb 15, 2010
Attempting to sum Book Values for Units.
Unit numbers in one column, Book values in adjacent column
Some Units have one occurence, others have multiple.
Some Book Values will sum up to 0 while most will have a positive value.
Attached shows small portion of data that presents range of issues I'm working with.
View 3 Replies
View Related
Nov 14, 2009
I need to create a report based on three columns. I have explained in detail about my requirement in the attached sample workbook.
I hope someone will help me to solve the problem through VBA because the items & expenses may vary at times. Hence, I feel a Macro will be the best solution.
View 14 Replies
View Related
Jan 16, 2009
I do not know if this is possible, I have a pivot table, however I would like to be able to sum a particular range based on start and end date. then by make and model as the second set of criteria, The sum would be displayed into a form on a different worksheet. attached is a file so I would like to know the total
View 3 Replies
View Related
Jul 22, 2014
I am trying to create an inventory control system that keeps tracks of what's in bins based on additions and subtractions to the bin. I am hoping to generate a summary output of bin contents based on the transaction list. I know how to do this using pivot tables, but am looking for a VBA or excel function solution since I will have hundreds of bins and thousands of transactions.
Bin number
Product Code
Pounds
1
A
50
1
A
-25
[Code] ......
I want to have the following table auto generated.
Bin
Product Code
Sum Quantity
1
A
25
1
D
75
[Code] .......
I'm using an array formula right now, but it is proving to be extremely slow.
=IF(ISERROR(INDEX('Harvest Generator'!$E:$I,SMALL(IF(LEFT('Harvest Generator'!$E:$E,LEN('Harvest Generator'!$E:$E))=LEFT(B$3,LEN(B$3)),ROW('Harvest Generator'!$E:$E)),ROW(1:1)),B$2)),"",INDEX('Harvest Generator'!$E:$I,SMALL(IF(LEFT('Harvest Generator'!$E:$E,LEN('Harvest Generator'!$E:$E))=LEFT(B$3,LEN(B$3)),ROW('Harvest Generator'!$E:$E)),ROW(1:1)),B$2))
Where Harvest Generator is the sheet where my table is on.
View 2 Replies
View Related
Nov 6, 2008
summing monthly values based on different date ranges. For example if I have multiple contracts with different start and end dates and based on those date ranges, I want to sum the respective monthly production totals, how would I do that in a formula. I started to set up the table to include "start date month" and "# of months in contract". Is there a way to sum based on the start month and # of months in contract.
For example if hte contract started in Jan08 and ends in May08, the calculation would start with month "1" and sum over 4 months.
Here is an example: ....
View 9 Replies
View Related
