Dynamic Sumif Function
Jun 3, 2009
I'm trying to create a sumif formula that finds the column the information is in that it needs to sum. I have attached a copy of the file I'm trying to create a formula to get results that I want.
Generally you would normally use the "=SUMIF(range,criteria,sumrange)". I was wondering if there was a formula I can substitute "sumrange" to also search a range of data (Eg: "G16:DA28"). I need the range to be dynamic as the column to be sum can change based on the criteria/option button being selected from the product category. I assume there is an indirect function involved, but I am not familiar with it. If you have a better and less complicated formula please suggest.
View 6 Replies
ADVERTISEMENT
Oct 25, 2007
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 9 Replies
View Related
Feb 4, 2007
I'm looking for a formula to help me handle dynamic sums. On worksheet 'A' I have 12 columns, the 12 months of the year. The rows are filled with expenses and the last row sums up the expenses for each month. In a different worksheet I want to present man info, but dynamically.
So if the first month is the start of a project, and it's the third month now, I would like to sum up the total expenses of months 1 and 2. If it's the fourth month now, I would like to sum up months 1,2 and 3.
I'm using a cell on the man info worksheet to store the number of months gone by.
View 9 Replies
View Related
Nov 1, 2012
I am currently recorded a macros for a sumif formula. But since the number of rows will not be same every time.
I want the code for a dynamic range.
VB:
Range("E7").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF('V1 Pivot'!R5C2:R32C2,'Task Level Tab'!R7C2:R30C2,'V1 Pivot'!R5C3:R32C3)"
Range("E7").Select
[Code] ....
View 9 Replies
View Related
May 1, 2006
I'm trying to use the Sumif formula when the "Sum_range" parameter is dynamic. I can identify the column number (by using "match" formula) but I have no idea how to use this information as a parameter in my SUMIF.
View 2 Replies
View Related
Mar 3, 2014
I'm looking for a way to write this:
[Code].....
as this :
[Code] .....
Where the criteria runs across a range (G to BG) that can sometimes have columns deleted, but the range must remain fixed.
View 3 Replies
View Related
Dec 1, 2007
I have a list of codes from which I'm trying to extract all unique entries.
For example, col A has 001, 001, 002, 003, 003, 003, 004, ...
I'm trying to create a report template with formulas that reference col A and return a single unique entry for each code so that the new column has only 001, 002, 003, 004.
Essentially, the codes reference customers. A customer can have multiple purchases -- but I'm trying to create a report in which I can utilize SumIf formulas to Sum the purchases for each customer and represent those purchases on a single line per customer code.
The catch is that the purchases change monthly (i.e., next month the purchases may be 001, 002, 002, 002, 004) so my report template needs to have the flexibility to adapt without me rewriting the SumIf formulas and return SumIf results for only customers 001, 002, 004. Whereas in the prior month, the report returned SumIf results for customers 001, 002, 003, 004.
View 9 Replies
View Related
Apr 17, 2009
I am trying to used the sumIF function in refer to a particular value, e.g. Name 1.
Question 1 - What formula I can used to make sure the SUMIF function refers to that specific value, e.g Name 1?
Question 2 - Is my SUMIF function correct to calculate the SUM of hours to a Task? This is due to the data table setting? find attached:
View 3 Replies
View Related
Jul 14, 2009
I am using Excel 2003. Worksheet named: Table_Data has the following code in cell: B2
View 3 Replies
View Related
Nov 6, 2009
Sum amts based on first left character of code
code amts
102 100
200 500
101 100
results
1 200
2 500
View 9 Replies
View Related
Aug 19, 2013
I'm having a little problem with the function Sumif. I have data like this:
Date
0
0
Total
0
78[code]...
I want to some all the Totals that appear before the "End Notification" text. I have more totals after that text and the number of rows are variable.
In my data, I have always 4 totals before that text, so I thought perhaps that the way to do this would be to impose to the sumif function to stop summing after counting 4 totals. I didn't find a way to do that.
I thought a little more on the problem and I find out that the easy way would be to record a macro with the sum if function, where, in the range part, I would do a Find for "End Notification"; and select all the cells to the top of the sheet. The problem is that the macro dont record that part, only the row number.I think I need to turn the range a variable, where the variable is equal to the find selection.
View 9 Replies
View Related
Sep 4, 2009
I’m using a SUMIF function to evaulate some date. The second argument in the function, the criteria, is that the numbers to be summed are greater than 25 but less than 50.
View 2 Replies
View Related
Dec 12, 2006
I want to add a twist to the sumIf-function, and need some help. My request is similar to lots of other sumIf-issues posted here, which were resolved with the sumproduct-function, but is a little bit different.
View 10 Replies
View Related
Jan 9, 2007
There is a function sumif() that is used for do sum() under condition
Nevertheless, if I want to do max() under condition, how can I do?
There isn't a function maxif().
View 9 Replies
View Related
Feb 27, 2012
How do I sumif on the amount in column A when the year in column B is 2010?
View 5 Replies
View Related
Jul 2, 2014
I was wondering if there was a way to use sumif and offset to guide the summing column in the example below. Perhaps I need to add an additional column, then formulate a solution. I want to sum the daily returns for the TSX when the slope is positive (green), and sum the S&P when it is negative. Currently, on signal day, I am summing that day's return, which I don't want to do. I want to sum starting on the next day (blue). The B column switches back and forth from positive to negative, so I will summing different data periods going back 1000 days. I am summing the TSX returns and S&P returns seperately to make it easier. Currently I have in C1=(sumif(b2:b1000,">0",c2:c1000)) Same for D2.
Date
Slope
TSX
S&P
sumif
sumif
06/30/14
0.002774
0.34%
0.19%
[code]....
View 3 Replies
View Related
Sep 1, 2006
I was wondering if it is possible to have a double sumif. For example asssuming you have a table with three columns:
Col 1 (far left) is called Name
Col 2 (middle) is called date
Col 3 (far right) is called values
Now assuming that column 1 contains 4 different names, each repeated for each date in column 2. In turns the dates are repeated for each names (see below example)
Name Date
AAA Jul 06
AAA Aug 06
AAA Sep 06
BBB Jul 06
BBB Aug 06
BBB Sep 06
CCC Jul 06
CCC Aug 06
CCC Sep 06
Column 3 contains random values.
How can I sumif column 1 at the same time as column 2 (i.e. sumif for criteria AAA and Aug 06). Or if not possible is it possible to write a double Vlookup?
View 9 Replies
View Related
Sep 18, 2007
I have a macro[A] that sums a column based on parameters passed to it from another macro[b]
Macro A
Sub SumColumns(varASheetName As Variant, varFormulaCell As Variant, varSumRange As Variant)
Sheets(varASheetName). Range(varFormulaCell).Value = Application.WorksheetFunction.Sum(varSumRange)
End Sub
' Macro B
Sub SumDebtorsStuff()
Call SumColumns("DebtorsRaw", "D10", Range("D21", Range("D65536").End(xlUp)))
End Sub ...........
View 9 Replies
View Related
Jan 8, 2014
Below is my function.
="Profit $"&SUM(IF(Sold!D15:D8998<>"",IF(MONTH(Sold!D15:D8998)=A4,Sold!H15:H8998)))
I would like to edit this function to also count profit only if Cell A15:A8998 in my Sold sheet has the words "Shipped". The text value Shipped is not a text value I entered, it is automatically written based on a Vlookup formula I created.
View 2 Replies
View Related
Nov 28, 2012
I have a drop list for selection of staff in an area, but can i then do another droplist and use the sumif function to sum up the total time depending on the value of the list.
i.e., if i selected name 1 it would search all of the sheet to see if name 1 was there and then add the times.
See attached : Utilisation.xls‎
View 2 Replies
View Related
Nov 24, 2009
I have a worksheet where two columns of data are written. In another worksheet I have a list of data in one column. In column next to it (so the rows are the same) I am trying to write a list of sumif formula, where the range is first column of the first worksheet, condition is a value of the cell next to where I am writing the sumif equation (list of data) and the sumrange is the second colummn in the first worksheet. I can't get it. I tried with --- "C" & i --- but I get an error saying expected:end of statement. It goes like that:
View 2 Replies
View Related
May 23, 2012
I know how to use the large function when looking for a specific criteria as the example below illustrates:
Code:
=IFERROR(LARGE(IF('2012'!$D$1:$D$7="Green",'2012'!$C$1:$C$7),1),"-")
What I'm trying to do is take this one step further and not only find the nth largest numbers in a set of data based on a particular criteria, but also sum those numbers because they repeat in a table: for example a sample table is below:
Account Accout # Store # channel $ sales
A 1000 10001 green $100
B 1001 10011 green $230
A 1000 10002 green $120
C 1002 10021 brown $145
A 1000 10003 green $100
D 1003 10031 red $20
B 1001 10012 green $50
So what I'm basically attempting to do is bring in the nth largest accounts within the "green" channel. Now if these were the top 5 stores I was looking for, the formula from above would suffice. However since this deals with accounts and the account # repeat I need to bring in the total sum of those repeating accounts instead of just one of the unique stores. So if I was seeking the largest account (NOT store) within the "green" channel the correct values this formula would be:
Account A $320
I would imagine we would need to combine a sumif with a large function or maybe involve sumproduct somehow.
View 6 Replies
View Related
Dec 22, 2013
I am trying to use a date range as a criteria in a SUMIF function. Below are the data and formula I am using.
Forecast
6
4
15
8
Week
1/12/2014
1/19/2014
1/26/2014
2/2/2014
Formula =SUMIF(G2:J2,">L1",G1:J1)
G1:J2 - raw data shown above. L1 = 2/2/2014. Desired result = 8. Result obtained with formula above = 0 how to make this work?
View 3 Replies
View Related
Oct 1, 2008
I am trying to link 2 excel sheets together and I am wondering if using the SUMIF Function is the best way to do this. I will try to explain and illustrate my position.
First I have a sheet called Master where I import my companies inventory and prices. It contains ALL our inventory. Looks like this:
(Column A) (Column B)
(Item Code (Item Price
(ROW1) THUMBRID $5.00
(ROW2) THUMTUBE $2.00
(ROW3) ZPIN $0.30
Now this master sheet is changing all the time as we import new products and prices all the time. So, this is a "list" where I can sort it by Item Code alphabetically.
Now, I am trying to make another worksheet with a list of what each of our kits contain and there prices. I will call this ThumbPiano. Like this:
Column A: COLUMN B:
THUMBRID $5.00
THUMTUBE $2.00
We have many different kits, that only take a couple of products, and our inventory list is at like 3,400. I want to be able to go into the Master sheet and update an Item price and have it automatically update it in whatever worksheet contains those items. But how can I do this if I add an item into the workseet. Then the cells move and whatever data was in that cell before is not there now.
So, that is why I chose the SUMIF function. Maybe there is a better one? Here's what I tried...assuming the two lists before, and the code Zpin is extra in the Inventory list because we do not use it in the thumb piano hardware pack.
I tried:
=SUMIF([Master.xls]Sheet1!$A$1:$A$3,A1:A2,[Master.xls]Sheet1!$B$1:$B$3)
It works, but then when I save it and close out of it and then re-open the ThumbPiano sheet it asks me if I want to update the sheet. I click yes, and then all the cell says "#VALUE!" "A value used in the formula is of the wrong data type." Please let me know what is going wrong. I know there has to be a way to do this. I just wonder if the SUMIF formula is not the way to link 2 sheets together...but if it isn't please keep in mind that I have this looking through 3,400 parts and I will be adding to them all the time, so it needs to search through all those parts and display the correct price....
View 9 Replies
View Related
Apr 22, 2006
I would like to create a UDF similar to the SUMIF function but the UDF would be dynamic in that it would sum based on a dynamic range that would change based on the row the user is in.
background: I have a very large input template for 12-18 months for multiple cost categories. I would like to have the UDF in a specific cell above each category that would show the user what the sum of the range in the current row they are inputing data. Each row represents a specific project/task (along with 12-15 descriptive columns) that makes the freeze pane option unusable.
View 5 Replies
View Related
May 4, 2007
Column A have random dates and column B have numbers. How can sum numbers for each month By sumif function?
View 6 Replies
View Related
Mar 9, 2014
I need the equivalent of a double conditional in the Function SUMIF.
In my example (a test file is attached), I have used hypothetical stock trades as a test case.
Column B has the type of order (Buy or Sell).
Column C has the stock trading symbol.
Column F has the net amount of the transaction on that line.
I want to populate Columns I & J, where I & J are the total Buy and Sell cost for each stock listed in Column H.
So, for example I need to calculate the total "Net Amount" of "Buy" transactions for Stock ABC, and put it in Cell I2. I also need to put the the total "Net Amount" of "Sell" transactions for Stock ABC, and put it in Cell J2.
I'm sure that looking at the attached sample EXCEL workbook, will make it clearer than all my verbiage.
So, conceptually, I need to evaluate Columns B, C and F and put the sum of transactions in Cell I2 for those cases with "Buy" in Column B AND "ABC" in Column C.
If there is no syntax for a double conditional in SUMIF, then I'd be happy with any formula for Cell I2 that accomplished the tax.
I'm not terribly familiar with array formulas, but have used them on occasion if that's what's required to emulate a double conditional in SUMIF.
View 9 Replies
View Related
Mar 10, 2009
I am using the following formuale to pick up a numeric value (from column G in Feb09 sheet: =INDEX(Feb09!G:G,MATCH($A10,Feb09!$B:$B,0))
Trouble is, the match it's doing in column b in Feb09 is listed twice in the sheet, but i'm only picking up the data for the value in the first line ... i think i need a sumif function.
View 2 Replies
View Related
May 24, 2012
I'm trying to add the total product sold by a group of sales person for example : Chad + Javier. My actual worksheet includes 600 rows of salespeople and 20 rows of product, i'm using this simple example to get the message across.
I was using =sum(sumif(A1:A9,"Chad',D1:D9),sumif(A1:A9,"Javier",D1:D9)), the formula should have given 91, but instead is calculating 113...
Salers PersonProduct AProduct BProduct C
Chad567
Chad567
Chad567
Chad567
[Code] .....
View 1 Replies
View Related
Feb 27, 2006
I need to get a subtotal of a range (eg: A1:A30), however some cells in this range have #N/A errors due to particular formulas they contain. I could go into these cells and add a formula which 'catches' these errors, but there are far too many given my current time constraints.
Is there any way to use SUMIF to add the cells which do not contain errors and skip the ones that do? (ie: SUMIF($A$1:$A$30,""&"Error Parameter")).
View 9 Replies
View Related
