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...............................
[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.....
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.
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?
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.
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 RelatedI'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.
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.
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.
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 RelatedI 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
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
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)
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
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.
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]....
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.
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.
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.
In my financial modelling I often have a calculated number of months of inventory. This number varies. I need to use this number to calculate the actual inventory value. For eg. it may be 3.2 months of inventory; in this case I need to sum 3 cells (current month, current month -1, current month -2) plus 0.2 of the fourth cell (current month -3)
But I may need to change the number of months of inventory to, for example, 4.2. In this case I would need the sum of four cells plus 0.2 of the fifth cell etc etc.
I am working on a sheet which calculates payroll. I have a list of people in column A, and in column B, i have their different professionnal status. Their wages vary according to their professionnal status and are in another column C. How can i sum up the wages of only certain people, based on their common professionnal status in column B?
View 3 Replies View RelatedI am trying to sum up a list of cells that have formulas attached so if there is no number in the formula it shows up as #N/A how do I make it so wherever there is an #N/A it equals 0?
View 9 Replies View RelatedI am attempting to use the if function that looks at a cell to see if it is a certain value and if so it adds the value of the cell above with another cell.
Here is the formula in cell AH26: =IF(G26=Variables!$F$4,AH25+AF26,AH25)
the problem is if the G25 did not equal Variables!F4 then AH25 is basically zero or the formula so when cell G26 does equal Variables!F4 then AH26 is supposed to sum AH25 with AF26 but AH25 cell's contents are a formula. How do I get it to ignore the formula and add AF26 with zero instead of the formula?
I'm trying to add cells that aren't in a range.
=AA60+AA41+AA22+AA3
Some of those cells are blank and I'm getting an error.
I want excel to treat those blank cells as zeros.
I am working on a sum formula for a column and I only want to sum if the value is a multiple of 15 or likewise if the value divided by 15 is an integer. I know that I could use several sumif statements to accomplish this, but there isn't really a maximum value. Is there a simpler way to accomplish this? I am comfortable enough with VBA so that is definitely an option as well.
View 4 Replies View RelatedI have concatenated the following cells:
A1, B1, C1
The data in each cell is:
1, +, 2
I can concatenate them to show "1+2".
My question is, can I then perform the calculation that the concatenation produces?
I would like to be able to enter different operators into B1 in order to carry out different calculations.
I'm using the following code to filter a particular range(it works perfectly fine). However I need to SUM Column 'L' once the data has be filtered. and place the result in the LASTS populated cell in Column L. At the moment I am selecting all the data in the column even the data that has been filtered out.
Sheets("Sheet1").Select
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:=">16", Operator:=xlAnd
I want to sum two cells, doesn't sound that difficult but I want to be able to change one of the cells over time. Hence, if B1 is the total sum cell, and A1 is what I fill in. B1 starts at 0, and if I fill something in in A1, B1 will sum itself and A1 (Unfortunately this gives me a circular reference).
So what I'm looking for is some way to use paste special automatically in the formula B1 I guess.
It is possible and how?
I have the following formula that is not working:
=(K72*0.2)+(N72*0.35)+(O72*0.25)+(P72*0.2)
It displays #VALUE! instead of the weighted sum I want. The reason for this is some of the cells are blank. If cells O72 and P72 are blank, how would I get it to sum just K72 and N72?
Other rows might have different missing cells, so the formula would have to work for different combinations of missing cells, but the weights will stay the same.
I have attached the file, in the last sheet (Consolidated) i need to get the data from all the previous sheets, my problem is that whenever i have similar cells the data that is showing is only the first value and not the sum of all values,
View 1 Replies View Related