Subtotal Function With Criteria
Jun 21, 2008
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
View 5 Replies
ADVERTISEMENT
Sep 22, 2007
I have a list of numbers that I wish to subtotal. I have tried the Subtotal function within Excel, but that is not giving me the format that I need. I have 4 columns. The Column A consists of account numbers.
I want to subtotal the debits and credits in Columns B & C, respectively, grouping them by the account number and then the date in Column D.
For example, after the solution, there would be a single line for each account number with the total of debits and credits per date (if there were debits and credits on multiple days, there would be totals for each day's transactions).
I am attaching an example to help visualize my situation.
View 4 Replies
View Related
May 22, 2008
I need a formula that will take this information and automatically insert a row, a total, and then another row every time the Product (Column C) or the Account Number (column B) changes. The totals should be under Columns D and E only. But I don't want it to duplicate the results when both the product and the Account number change.
Beginning data: ...
View 6 Replies
View Related
Oct 13, 2005
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)
View 10 Replies
View Related
Jul 26, 2007
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?
View 3 Replies
View Related
Jul 21, 2014
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.
View 11 Replies
View Related
Feb 9, 2012
(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.
View 2 Replies
View Related
Jan 14, 2013
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.
Code:
Sub CountNewLoans()
Dim cnlFinalRow As Integer
Dim cnlRow As Integer
Dim x As String
Dim y As String
[Code] ......
View 6 Replies
View Related
May 28, 2013
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?
View 3 Replies
View Related
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.
View 2 Replies
View Related
Feb 21, 2014
I'm trying to exclude cells that are filtered in a separate sheet.
The sheet I'm working with gathers information from this separate sheet using the following SUMIF formula:
=SUMIFS(Claims!$H:$H,Claims!$G:$G,$A9,Claims!$B:$B,C$6,Claims!$E:$E,$B9)
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?
View 5 Replies
View Related
Feb 5, 2007
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?
View 12 Replies
View Related
Oct 23, 2008
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.....
View 10 Replies
View Related
Dec 6, 2007
I have a large transactions table with 10 columns and more than 7,000 rows. One of the columns names the country in which the transaction has taken place (array named "country")
Another names the year of the transaction (array named "year")
And a third one holds the transaction value. (array named "value")
What I would like to do is to sum the transaction value of one country for one specific year? I.e. what was the aggregate transaction value for transactions in Sweden in 2005?
View 3 Replies
View Related
Sep 15, 2014
I run a countifs against a column of data, and can easily test for specific concrete criteria in other columns, but I want to run a function on the value in a column as a criteria.
For example,
countifs(data Name, "=Technical", data count, ">4")
is great.
But I want to check for some function on another column, e.g. "data number, square(x)<100", I cannot reference the indexed data value in the (data number) column as the "x" in the formula.
What I really want to do (!) is to test if a field is (not) in a list of matches, e.g.
..., data tag, NOT(ISERROR(MATCH(???,GoodValues))),
Where I can see if the tag for an entry is in a list ("GoodValues") defined elsewhere.
View 1 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
Dec 15, 2013
I am stuck trying to add another criteria to an existing formula.
On my sheet "Data" i have the following formula in column AF2
=IF(Z2="","A",Z2-AE2)
If Z2 (Payment Date) is Blank then return "A" in AF2, however if Payment Date has an actual date in the cell then minus AE2 (Received Date) from the Payment date.
This gives me the "Receipt to Pay" number E.g. 6 days from receiving to paying an invoice.
I now need to add another criteria to this and i cant fathom it.
I need to do the following:
If Payment Date (Z1) is Blank and Invoice Status (T2) is "Loaded" then Minus Received Date (AE2) from Todays Date, while also retaining the last part of original Formula Z2-AE2 if the first criteria is not met.
(I have put =TODAY() in Cell AG1 for ease).
What do i need to do to add this into the formula. Do i need to use IFS function?
View 3 Replies
View Related
Jan 21, 2014
Criteria:
U4= 12/01/2013
Data:
(A) (B) (C)
01/01/2013 02/01/2013 12/01/2013
1 3 4
2 2 1
4 1 1
Result:
Small - looking for smallest to fifth smallest (separate cells B5=1, B6=2 etc.)
View 1 Replies
View Related
Apr 15, 2009
I have the following formula below to return "C" if the value in the cell is "C", and nothing if not.
I want to expand it to return "C" if the value in the cell is "C" or "D", for a range between C2:H2.
=IF(C2="C","C","")
I'm have trouble with this easy adjustment.
View 3 Replies
View Related
Feb 4, 2010
MathScienceLanguageOverall
PassPassPass----
PassFailPass----
PassFailFail----
FailFailFail----
What formula should I use to show "Pass" if a student passed all three subjects, "FAIL" If student did not pass in any subject and "TRY AGAIN" if he passed at least one subject? I can't seem to accurately plot "IF" formula. Should add in "AND" / "OR" as well?
View 5 Replies
View Related
May 5, 2009
Bid Manager Probability of Success Total Fee
cp 25% 675,000
rz 40% 18,383,541
nw 50% 8,700,000
cp 10% 4,500,000
ap 50% 1,200,000
kd 50% 49,500
sl 25% 3,200,000
bc 20% 310,000
sl 20% 3,460,000
nm 30% 6,750,000
at 50% 23,000,000
ap 30% 367,500
rn 40% 13,750,000
I have a list of commodity and percentages of success with fees in the 3rd column
I need a function to count the records with multiple criteria e.g greater than 20% but less than 30%.
Second i need a function when it complies to previous formula it should give fee total pertaining to that count.
View 2 Replies
View Related
Aug 21, 2007
I have a condition that it only applies to criteria before a certain date but I cannot get it to give me the right answer.
Original Formula ...
View 3 Replies
View Related
May 22, 2008
I would like a consistent OFFSET function formula across a row but my columns move from monthly to semi-annual periods at a point in time. How do I handle the move from monthly to semi-annual periods in my OFFSET formula?
View 4 Replies
View Related
Aug 21, 2013
Excel.xlsx
Attached is the example of the data, I am trying to sum the amount applying 2 criteria, within a particular month and other category. The formula tried is
=SUMIFS(D5:D10,C5:C10,"*June",E5:E10,H1)
However it is giving a value of zero only. I have tried other combinations as well.
View 4 Replies
View Related
Apr 20, 2014
I need to do a SUMIFS with 2 sets of Criteria, but I need the second Criteria to be an OR Statement. The numbers I am adding up is in Column E, with any where from about 20,000 to about 60,000 records (will fluctuate all the time). The first Criteria is easy - match up the name in AG12 with any of the names in Column C. The second criteria is to match up the numbers in Column A with any of the numbers in AD1 through AD40.
I tried this but it didn't work: =SUMIFS(E:E,C:C,AG12,A:A,AD1:AD40). I've alos tried a million other variations of that, imbedded SUM(IF( statements, SUMPRODUCT statements, and DSUM statements and no luck. I can't find anything that allows me to make that second second criteria look at the numbers in Column AD as being AD1 or AD2 or AD3 so on and so forth.
View 3 Replies
View Related
Aug 10, 2014
I'm working on a spreadsheet that I need to return a value to "Unit Price" field in worksheet "Master Inventory" based on matching the "Product" field and the "Construction" field from the "Unit Pricing" worksheet.
In essence, I would like the "Unit Price" field to match the "Product" field from the "Master Inventory" sheet to the "New Product Description" field on the "Unit Pricing" sheet, then match the "Construction" field on the "Master Inventory" sheet to the column headers on the "Unit Pricing" sheet and return the value that corresponds to both criteria.
Ex: On the "Master Inventory" sheet, I would like the "Unit Price" field to match the "Product" (Book Browser) to the "New Product Description" (Book Browser) on the "Unit Pricing" sheet and then return the value where the "Construction" (Laminate) matches the column header (Laminate) on the "Unit Pricing" sheet which would return the value of "$240.00".
I've tried using a vlookup function, vlookup/match function, index/match function and an index/match/match function. I've attached a sample workbook.
View 3 Replies
View Related
Feb 5, 2014
=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480)*(MONTH(Sold!$M$15:$M$8998)=12)),"")
I would like to edit the function above and add a third criteria. If Sold!S:S,"Returns" to my sumproduct.
I tried to edit it myself with the function below but it didn't work.
=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480,Sold!S:S,"Returns")*(MONTH(Sold!$M$15:$M$8998)=12)),"")
View 3 Replies
View Related
Jan 22, 2007
I am trying to use the function DGET() to re-organize the information on the "DbCalc" sheet in the attached workbook. This formula can be found in C2:C5 in "purple. I am referencing the worksheet "Exp Rpt" as a database and match info based on two criteria. I can do this using an array formula like this ={SUM(IF((A1='Exp Rpt'!B$9:B$10000)*(A2='Exp Rpt'!E$9:E$10000),'Exp Rpt'!H$9:H$10000,0))} , however with the amount of data that I need to reference this array is soaking up all my memory. I would usually turn to access to do this, but the person that will be utilizing this tool only knows excel.
View 11 Replies
View Related
Dec 2, 2008
I have the following formula in a sheet to record basketball stats that I am putting together.
=COUNTIFS('Running Game Log'!A:A,"=Jarod",'Running Game Log'!C:C,"=2 pt miss")
This formula counts from a list that is generated during in-game recording of stats & obviously counts the number of times "Jarod" misses a "2 pt shot".
My question is can the criteria "Jarod" refer to a value in a cell, rather than the specific name "Jarod" as I want to be able to change player names easily, rather than have to edit this formula every time.
View 7 Replies
View Related
Apr 30, 2009
I have table product that include (wide 1,wide 2,thick 1,thick 2,long 1,long 2,Price) And I have table transaction than have wide, thick, long and price)
I want that price in table transaction fill automaticly from table product where ( wide between wide 1 and wide 2 ) and
( thick between thick 1 and thick 2 ) and ( long between long 1 and long 2 )
excelformula.gif
pricing.xls
View 2 Replies
View Related
