Use The Worksheet Function Vlookup On Subtotal Values
Jul 12, 2006
Is it possible to use the worksheet function Vlookup on subtotal values? If so what is the formula? The problem that I am trying to solve is as follows. I have a sheet (1) listing part numbers that are not moving at a particular depot. On another sheet (2) there are details of sales relating to the non-moving part numbers at 6 other depots, I have added subtotals. Currently I am switching between worksheets to see if I can transfer stock. I would like to id on sheet 1 the total number of sales at other depots, then I can see what part numbers may be transferrable without a great deal of manual intervention.
I have a workbook with two tabs, Summary & January. The january tab has total expenses for the month of january that are subtotaled by last name. i.e. (baker total, decker total..). I would like to be able to use a formula on the summary tab to pull the values of each of the totals for each person from the january tab to the summary tab.
I tried on the summary sheet doing =jan and selecting the first persons name and then going back to the summary tab and dragging that formula down but it pulls all of the other data within the subtotal on the jan tab and I just want each persons total.
I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese John Chinese - Simplified John Korean Martin Arabic Martin Chinese - Simplified Martin Russian Ramon Arabic Ramon Russian Sam Arabic Sam Chinese- Traditional
I am using the VLOOKUP function to get the exact corresponding value in one particular column. But I need to have two different value in two different columns. Here is an example.
I have attached the excel sheet as well.
Actually I do send the data to my different colleagues and they do send me back with the responses in two different column of D and E corresponding to the "ID" specified in column "B". Which I do need to MAP individually. But I want Excel to map the value of that "ID" (column B) to the corresponding responses in Column D and E with formula.
VLOOKUP generally gives corresponding value to either column D or E but i want both at a time to save time.
Column A includes a name Column B includes an age Column C states a division (this is the column that contains the Vlookup function)
Column G is where I have to put a range of ages (for instance, x=<0,>3) Column H is where the division name is.
The idea behind this exercise is to use the Vlookup function in column C to reference column G, and return a value in column H. Here a copy of my formula in column C: =VLOOKUP(B3,$G$3:$H$12,2,TRUE). For instance, if Mike (A) is 16 (B) then he would be in the Midget division (C). C is calculated by comparing column B to G, then returning a value in column H. For some odd reason, I can't get column G to reference an age range, such as 1-5 years old.
I would like to copy values corresponding to a unique identifier from one sheet to another, using the vlookup function. The problem is that after a certain cell the values are given as N/A even though they are present in the sheet I want to copy them from.
I have got a multisheets database where both rows and columns may be hidden according to a given criteria. Only blanks rows and columns should be hidden.
Now I need a formula to check if there is no "valuable" data hidden.
Checking test might be: SUBTOTAL(9,reference) = SUBTOTAL(109,reference)
I have a spreadsheet that deals with payments and has subtotals in it. Every subtotal formula begins like this: =SUBTOTALS(9,H212:H225). My question is: What does the "9" and the "comma" mean?
I would to use the subtotal command and have it exclude rows from another column in addition to obeying an autofilter:
A B C 1 Area Rep Name Amount 2 Quota John 100 3 NW John 200 4 NW John 300
I want to use the subtotal(9) on the amount column, but I don't want it to include rows where the value Quota is in the area column. The idea is that the amount for a given autofilter condition (rep = john) should only show his forecast amounts but not his quota number for his area. DSUM includes hidden row as does sum
I have tried
subtotal(9,sum(C2:C4*(A2:A4<>"Quota")))
but that does not work
=SUM(C2:C4*(A2:A4<>"Quota"))
works but does not let me filter the data to only show values for john versus all reps
I'm trying to calculate the mode for a large data set, but there is no 'mode function' in subtotal. I need to find the mode for each change in day - without having to retype the function.
(I've actually gotten what I needed through some very convoluted formulas, but I thought there MUST be an easier way!!)
I have three columns of data: foldername1TEST0001TEST0006 foldername1TEST0007TEST0008 foldername1TEST0009TEST0018 foldername1TEST0019TEST0021 foldername1TEST0022TEST0022 foldername1TEST0023TEST0028 another folderTEST0029TEST0031 another folderTEST0032TEST0039 another folderTEST0040TEST0056 yetanotherTEST0057TEST0058 yetanotherTEST0059TEST0101 yetanotherTEST0102TEST0104 yetanotherTEST0105TEST0106 yetanotherTEST0107TEST0154 yetanotherTEST0155TEST0190 foldername1TEST0191TEST0197 foldername1TEST0198TEST0267 foldername1TEST0268TEST0275
I am trying to get a "range" for each folder using the first instance in columnB and the last instance in columnC. For example, what I need to end up with is:
foldername1TEST0001TEST0028 another folderTEST0029TEST0056 yetanotherTEST0057TEST0190 foldername1TEST0191TEST0275
Again, I was able to finally accomplish this, but it took some way outside the box steps. Tried it in Access with simple query for Min and Max, but the caveat is that there are identical folder names for different ranges (see "foldername1) that need to be captured and I was getting results like "foldername1 TEST0001 TEST0275", which is incorrect.
I am trying to create a macro which will use the subtotal function to do a count on all the the new loans in column A. The could below is what I have tried but I am getting a run-time error 13 "Type Mismatch" error at the line highlighted in red in the code. How to write the Subtotal function in the macro.
I am using VBA to get the data from a larger worksheet.
Is there a way to get 2 subtotals in the sheet with only the 8 columns.
I have grouped this data by date (column 1) and used SUM on Column 8 to get the subtotals of each group.
I would also like to get the COUNT of the items of each groups by using Column 3 (it has alphanumeric data in it) - at the same time. Is this possible?
If not, how would I be able to get the COUNT without wiping out the SUM subtotals? This has to be done with VBA of course.
I'm looking for a formula that acts the same way as the subtotal formula (revises if rows are hidden and only adds visible rows). Is there anyway to get this removal of hidden rows but for subtraction?
This formula extrapolates information perfectly for me if I do not filter columns in the separate sheet with all of the information.
I know that SUBTOTAL functions can be used in a way to exclude hidden cells. Can I combine the SUBTOTAL function into my SUMIF function above to produce results that exclude filtered cells?
Someone has sent me a spreadsheet and there are subtotals on it. I want to remove the subtotals add loads of new lines and then subtotal it again rather than trying to scroll though each line and add stuff. However I'm not sure how the subtotal is set up is there an easy way to identify what the subtotal consists of?
I have a large list of data including subtotals. I want to copy and paste just the values of the subtotals to another worksheet. I have tried collapsing the list to Level 2 and copying/pasting values but this still carries over the whole list.
I have a data set of students results and I have been using countif to pickup similar grades eg, C1, C2 and C3 all as C. However I need to subtotal filter against teachers and the sumproduct won't let me use C* as a wildcard to pick up the three types of C. How I can subtoal countif multiple criteria.
I have data in a pivot table that has some positive and negative values. At the end of the Pivot Table, it sums the values for each month and then a grand total. I would like to also have it show the summation of all negative values per month. So it would be:Jan Total Jan Negative Total Feb Total Feb Negative Total etc... Grand Total Grand Negative Total Is it possible to add such functionality?
I'm looking for a SUBTOTAl IF function (if there's any) that would sum all the distinct values in a column for a given criteria based on another column. My data is listed below:
I'm looking for a formula to sum the DISTINCT Revenue values for Q1 2008, Q2 2008, and Q3 2008 based on the autofiltered list. The formula result should change dynamically based on the autofilter selection.
way to do this but i have a sheet that is into 5 - 6 thous rows, in one of the columns (names) i sort it by names and then order it by subtotal for certain values.
What i need to know, is there anyway i can take just the subtotal values out and put onto another spreadsheet without copying and pasting it all as there are lots of subtotals and this would help alot as the other info is not nec. just the subtotal'd info. either that or is there anyway i can highlight the subtotal'd row info in yellow/bold text anything like that that would make it stand out without having to do it manually?
I’m trying to get my sheet so that at each change in month it creates a sum of the value but I want to sum to show up in the subtotal value column.....
=SUBTOTAL(2,A2:A100) can be used in combination with any other formula so that when the data is filtered using autofilter it will only count values greater than zero.
I have attached a worksheet with the example.
In the attached workbook subtotal formulas are used in Row1 for sum and row 2 for count. When the data is filtered using code "DUP" in column D, the subtotal in cell C2 is "4", whereas in the specified range there is a positive value in only 1 cell that is $1050 in cell C99.
Is there some combination that can be used with SUBTOTAL formula so that it will only count values greater than zero when data is filtered.
let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.
[Code]....
This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.
I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:
This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in Match: Lookup value = $E3 Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1 match type = 0
This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered
Match: Lookup value = $A$4 Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452 match type = 0
Index: array = $BA$434:$DN$452
So I think my final function is
[Code] .....
But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.
The Room ID values in Column A are associated with the Room values in Column B. I'm trying to move the values in Column A Room ID to Column G Room ID by having excel look up value in Column C Room or Area #, compare it to Room, associate that with Room ID and automatically fill in Column G Room ID. There are 1000s of these so it's not possible to do it by hand.
I attached a picture where i had 2 different workbooks. In reality, I'm working off of 2 worksheets within a workbook.Excel Question.jpg
I tried both IF and LOOKUP and failed. I'm trying to search for values from one worksheet and identify whether or not those values exist in another worksheet. I attempted the following lookup in field A2:
=LOOKUP(B2,Sheet3!A$2:A$914,Sheet3!C$2:C$914)
B2 (thru B5000 or so) contains values I want to search for; sheet3!A$2:A914 is where I want to look and column C of that same sheet, entered the text "Yes" in an attempt to have the results list "Yes" for hits and N/A for misses. (All fields are text.) I copied the formula all the way down the sheet in column A. The result it is returning is N/A in A2 and Yes in A2 -to the bottom, which is incorrect.
I am trying to use the vlookup function together with an offset function but i am not getting it to work properly.
The situation is as follows:
I have a column E in which i use the vlookup function to find its corresponding value in B which in turn refers to a named range. However, the figure i want the function to return is 2 columns to the right and 2 rows above the value which the vlookup funtion finds in the first column.
I have though of using the offset function, but i cannot figure out how to make this work.
