Large Function Combined With Sumif
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
ADVERTISEMENT
Jun 19, 2014
I am having a list consisting of two columns. Column A describes a "product" and the currency directly below. In column B i have the quantity of the product and below the value. Now i have products in USD and EUR and would like to only sum up the quantity of all products which are in USD. A simple example is attached and i calculated the target valua manually. The values i would like to sum are market in red.
View 1 Replies
View Related
Oct 29, 2008
i would to combined these together
can this be done
View 2 Replies
View Related
Feb 13, 2014
I have a report I am attempting to populate with data from a pivot table in another worksheet. Column A holds all the reference numbers (primary key), column B contains various start dates, and I want column C to contain all the payments made since the start date for each reference number.
The source data is a pivot table with Row = Reference number, column = transaction date, values = transaction amounts. This is an extremely large table, as I'm processing data from almost 1,200 cases, which each have around 20 payments spread over the last year, on completely random days. What I would like to do is build a formula in my report which looks up the records for the reference number from column A, and then adds up all the payments which have been made after the date in column B (and ignore any payments in the table which are before that date).
And to make things more complicated:
if an error is generated, it needs to return as 0, not #N/AThe report has the dates in UK format dd/mm/yyyy, but the pivot table has the dates in SQL format: yyyy-mm-ddThe pivot table is connected to a SQL database via ODBC and has to refresh every time it is opened.
=IF(ISERROR(VLOOKUP(A2,'Transactions'!$A$2:$B$1194,2,FALSE)),0,VLOOKUP(A2,'Transactions'!$A$2:$B$1194,2,FALSE))
View 5 Replies
View Related
Jul 12, 2014
I am attempting to create a vlookup formula that will count or sum a series of data so that I can pull it over into a summary sheet on a report. The vlookup formula that I am using is
=IF(ISNA(VLOOKUP($A34,'Jan 14'!$N$2:$AF$36,12,FALSE)),0,VLOOKUP($A34,'Jan 14'!$N$2:$AF$36,12,FALSE))
It seems to be working I just cant figure out how to incorporate the count or sum function into it where I need it.
Test Sample File.xlsx
I have attached a sample of the file I am working with.
View 4 Replies
View Related
Mar 4, 2014
I'm trying to run a match function combined with If and Iserror. I need this because I want to see if a given item number in one cell in one sheet in excel is ALSO located in a given range in another excel sheet. If that number is found, I want the result to say "yes". If the item number is NOT also found in the given range in the other workbook, I want it to say "no". This is what I have so far:
=IF(ISERROR(MATCH($C9,'Case Usage Summary'!$A$2:$A$45138,0))=FALSE,"Yes","No")
The problem so far is that this is actually giving me the wrong results. For instance, the above function attempts to located cell C9, denoted as item number 964, in another sheet called Case Usage Summary in the range between A2 and A45138. If it could find 964 in that range in the Case Usage Summary sheet, it should give an answer of "yes", if it couldn't then it should give an answer of "no". The problem is that it gave me an answer of "no", but I just looked through the list of item numbers in the case usage summary sheet and there IS in fact a item number 964 entered in the range. What is going on here? I tried this for some of the other cells, and it is just giving me "no" for all of them. What is going on?
View 9 Replies
View Related
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
Jul 2, 2013
I have a table of sales by month per account. Each account has an associated country, but there may be many duplicates of each country per month.
I want to create a formula which indexes the country field and gives me the country with the highest total sum for a specified date range. I'd like to be able to drag the formula down and have the country with the highest sales, then the second highest, and third highest, and so-on. Column ranges are specified below.
K - L - M - N
------------------------------------------------
Month
Year
CtryDsc
Rev
1
2011
[code].....
I was thinking of using an array function utilizing index, large, sumif, and match, but I am getting stuck. I'm honestly not sure if what I'm looking for is even possible, but in Excel, everything is possible, so there has to be a way!
View 4 Replies
View Related
Jul 14, 2009
I'm trying to create a mini-table that will give me the 3 highest and lowest values in a range (I know how to do this using LARGE and SMALL functions), but I also want to get the corresponding name (in column A) for each number. This last part I don't know how to do.
I've attached an example sheet that I hope gives a basic idea of what I'm trying to do. I'm looking for a method to fill in the data for cells A13:A15 and C13:C15 in the attached sheet.
I could modify my worksheet a bit and probably get the result I'm looking for, but I'm hoping to come up with a nicer solution, and hopefully learn Excel a bit better in the process.
View 2 Replies
View Related
Aug 24, 2014
I was doing this task using sorting then adding column today morning one of my friend told i can do it with formula only, no need to using sort and adding new column
I just attached the sheet : without sorting.xlsx
View 5 Replies
View Related
Jun 8, 2007
Column C contains the names of real-estate brokers.
Column E contains the colors of the homes for sale.
Column Q contains the values of the homes.
I want to return the sum of the 50 most expensive homes that are yellow and for sale by Century 21.
I'm familiar with array formulas, and with the large function. I just do not know how to incorporate the large function within the sum/array formula.
View 9 Replies
View Related
Oct 22, 2013
i have two excel files which are data and master..both files will be use for salary calculating.. the vlookup formula will be use in master files for dragging the salary data from Data files..the formula as follow VLOOKUP(B4:B225,'D:Salary[Data.xls]AUG'!A$1:F$65536,6,FALSE))
the vlookup working fine to me.. but my problems is i want the data to be auto calculated when they have same value in two columns.. or if the name is similar/match in two columns (one for salary and another one for overtime), the salary should be auto calculated.
Data files will contain of these:
a:Employee ID
b:employee name
c:Employee salary/Overtime
Master files will contain of these:
a:Employee ID
b:Emplyee Name
c:Employee Nett Salary (that will be dragging from Data files)
is there any formula that i can used to combined with my vlookup formula?
View 4 Replies
View Related
Feb 1, 2010
I am trying to use the large function over two ranges. Even though incorrect I will include my original formula that doesn't work to help illustrate.
if(large((L20:L500,W20:W500),1)=I20,$P$4,if((large((L20:L500,W20:W500),2)=I20,$P$5,0)))
Does anybody know of an easy way around this?
View 6 Replies
View Related
Mar 7, 2006
Excel vlookup function returns #VALUE when the lookup value exceeds 256
characters. I need a hash function to transform large strings into a value
that does not exceed the 256 character limit.
View 14 Replies
View Related
Apr 5, 2012
I use a large function when ranking numbers in an array each month. I'm only interested in the top 5 numbers. However, there are occasions when the top 5 numbers contain a tie. How do you build into a large function, logic to handle a tie. Here's my function, which is very simple:
Code:
=LARGE(C$6:C$40,1)
=LARGE(C$6:C$40,2)
ETC.
View 1 Replies
View Related
Feb 9, 2009
I'm trying to use the Large function with multiple cells to get the highest value of the cells. Excel won't let me use the following formula.
=large({a1,f1,j1,o1},1)
Any other ways to get the largest value from multiple cells with or without an array.
View 9 Replies
View Related
Sep 3, 2009
I have a column of number that I want to use the Small & Large function on, skipping duplicates.
Example:
Column A has the following numbers;
1
6
1
4
3
2
4
6
17
8
21
8
10
13
I want the Small function to identify the first lowest number (1) and then the second lowest number (2), not (1) again.
View 9 Replies
View Related
Aug 1, 2006
I have a large amount of data, in columnA a list of cars, columnB a list of engines, in columnC the city built, in columnD the country to ship, in columnE distance, in columnF the time.
Now some makes, city's and destinations match and sometimes the engine, but i would like to be able to say cellX = (time to ship) where cellA = Ford AND cellB = 1.8 AND cellC = London and CellD = Ireland
The key data is the city built and shipping destination, so i'd like to say if cell A3 = London AND Cell B3 = Ireland AND Cell A5 = Ford AND CellA7 = 1.8 then CellA10 = (distance) & CellA11 = (time)
Vlookup's are of no use due to the large amount of data. But is there a way of writting this command in VB?? or am i missing something in Excel?
View 6 Replies
View Related
May 6, 2007
I am trying to use the 'large' function to input data from multiple spreadsheets, but am uncertain how to do so other than to use the same rows/columns in each and to use only continuous worksheets (that is, I want to enter data from worksheet 1,3,5 and different columns in each, for example).
View 4 Replies
View Related
Jun 9, 2007
I read the thread Converting from Hexadecimal to Binary and what I need to do is convert the following hex number to binary; 9E6799CF. The function in that thread doesn't seem to handle that large of a hex value. Is there anyway of doing that with a HexToBin function, short of chopping it up converting the smaller numbers and recombining it in binary?
View 2 Replies
View Related
Jun 14, 2014
I have been facing few problems while automation my production sheet through formula.
1-- I want to sort data in the descending order through below formula.
=INDEX($A$23:$C$29,MATCH(LARGE($C$23:$C$29,ROW(B3)),$C$23:$C$29,0),MATCH(H$22,$A$22:$C$22,0))
when two candidates have the same percentage then this formula does not work.
View 1 Replies
View Related
May 18, 2009
I want to create a user input for a "LARGE" function so that I can then return the specified # of "large" items. i.e. user wants to get the top X number of users, user then enters number either into a dialog box or cell, code then looks at the number and populates a range of cells based on the input value. The current code that I am using simply refers to an existing table (r9:r30) that I then used the auto fill to copy to cells below it.
=INDEX(Sheet2!B$20:B$961,MATCH((LARGE(Sheet2!$F$20:$F$961,$R9)),Sheet2!$F$20:$F$961,0))
I really want to be able to just get a user dialog box going that will automatically populate both the range r9:rx (where X is the user input) and then also copy the above formula into the x number of cells below it.
View 2 Replies
View Related
Oct 2, 2012
How to get the LARGE function to ignore hidden cells? Is it possible?
I have a filter in a data set and am using a macro to pull in the top 5 largest values.
View 3 Replies
View Related
Dec 17, 2012
I have 3 columns of data: col. A = name (random order), col. B = Net #, Col. C = Gross #.
I am using =LARGE(C$1:C$4466,ROWS($D$1:D1) to Automatically sort col C in decending order.
I would Like to do another decending sort but only the values in Col C that corespond to a particular name in Col A. Can I imbed a index match function combination within the large function to do this?
Name
Net
Gross
All Sort
SortA
SortB
SortC
SortD
A
508
-200.129
101.685
[Code] ..........
View 6 Replies
View Related
Jul 23, 2013
I need to add something to my macro to delete a sheet if it doesn't have more than 40 rows of data. If it has more than that, it needs to do a whole bunch of stuff to it. I've already got the whole bunch of stuff written, I'm just not sure how to do the if function. I took a stab at it, but it wants the count to be an object. I am not very good at writing code.
Code:
Dim count As Integer
Set count = "COUNT('33M & 33RUL'!A:A)"
If waffle Is "
View 1 Replies
View Related
Dec 19, 2007
could provide me a loop function in VBA for these calcultions.
Cell A1*E1 + B1*F1 + C1*G1
Cell A2*E1 + B2*F1 + C2*G1
......till
CellA100*E1 + B100*F1 + C100 * G1
These are the calculations for E1-G1.
The process must be repeated till E50-G50
So the second step is:
Cell A1*E2 + B1*F2 + C1*G2
Cell A2*E2 + B2*F2 + C2*G2
......till
CellA100*E2 + B100*F2 + C100 * G2
View 12 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
Nov 27, 2012
I am trying to insert three columns within a large amount of data. I am using Excel 2003 edition. The three columns need to measure max, min, and standard deviation of month long ranges and the data goes all the way back to 1993.
Currently, I have a column that has the correct ranges but finds the average for each month
(=AVERAGE($H7214:$H7243))...
And many more ranges as it dates back all the way to '93. Is there a possible way to insert these three columns with their respective commands (=MAX... =MIN... etc.) while keeping all the ranges from the AVERAGE column.
In effect, I am looking to solely switch the begining of the column command
(=AVERAGE($H7214:$H7243)) to (=MIN($H7214:$H7243) etc...
While keeping all of the specified ranges from the AVERAGE column.
View 2 Replies
View Related