DSUM Formula With Varying Criteria Across Report Matrix
Sep 21, 2006
I currently have a report with so many large array formulas that it is virtually unusable. I want to use the DSUM formula in place of my array formulas but I am vexed in regards to creating a DSUM formula that I can put in the top-left cell of my report and then copy down to the bottom right side of my report. Currently, the array-formulas sum data from a large list when it meets two criteria -- one part of the data-record in the list must match the row label of the current row in the report and another part of the data-record in the list must match the column label in the current column of the report. I have found with the DSUM formula that you can use a "formula" criteria in place of a static criteria...the problem I am having is that the part of the formula that points to the data-set must be relative while the part that points to the formulas criteria needs to be static -- in order to be able to make one DSUM formula and copy it down and over I would need my formula criteria to have the opposite setup (with the data-set part static and the formula criteria as relative).
My last post here was brilliant, I was very close to figuring the problem out myself (I'm learning) and the help I got here really was useful to help me finally get what i wanted to work. Unfortunately, this request isn't so simple, as I haven't a clue what to even search the forum for to help me with this.
I have attached a simplified example to try and explain what i would like to happen.
Sheet1 holds information which is regularly updated, so the number of rows can vary, as the number of customers vary.
I want to produce a management report from this data, with a specific layout. I have put the layout into Sheet2 - the cells highlighted in yellow are there simply as a reference to show the cell from which column would go where.
I produced the top report, by filling the cells with =and then clicking on the cell on sheet1 that I wanted displayed there. I thought that if I then selected the table and filling down, it would produce the same layout table for all the other rows - but I was wrong.
Another problem is that, this report is needed to be entirely automatic, so I don't want to fettle filling down cells for the number of rows. I will need it to count the number of rows in Sheet1 and produce a report in the specific layout for each company in sheet 2.
It may be an option to use a button create the report, to save the report into a new workbook with automatic file name containing the date and also print a copy out automatically on the default printer.
I don't know how to go about this task at all, so I would really appreciate some help, even if it is pointers for what i should be searching the forum for!
I have a list of data with salesperson and qty. (Excel 2003)
I am using dsum and it was working great until I add a new sales man who happened to be the son of another salesman.
example. when I just had Bill Gates his total (DSUM) was fine. now I added Bill Gates Jr. since then Bill Gates totals now include Bill Gates AND Bill Gates Jr. FYI - Bill Gates Jr. totals are correct.
I have a workbook with three diffrent sheets (and more, but they are not relevant here), each with its own webquery. After a query has been done I check with the OnChange- event, and that works fine. My problem is that I want to save the document after the queries has been done, but if I save it after the first one is done, the second and third queries are aborted. Is there any way to see if a query is being refreshed or not. If I can see that a query is refreshing, I can tell the macro not to save, and when the last one is done it will save there instead (I have no idea if its going to be the first, second or third query thats being refreshed last, as they take diffrent time to perform). I was thinking of something similar to:
Sheets("Sheet2").Select If Not ActiveSheet.QueryTable.Refresh Then Call SaveDocument End If
Now, this doesnt work since the querytable doesnt have the refresh-propery to cehck, but its something like this I would like.
You can see there is an equation for: The total profit from apple trees. The maximum profit of apple and pear trees. But, there is no example for the total profit from pear trees.
I have an excel spreadsheet that I need DSUM to work not only for A1:A2 (apple tree), But for A3 (pear tree), A4 (another tree), ext. But I don't want it to sum A1:A4 (A1 + A2 + A3 +A4), I just want A4's sum. Or from the example, I want just A3's sum for just pears, but when I put in A1:A3, I get the some for both apples and pears.
Ihave the following headings in a database. CSAMeeting DateMeeting typeCompanyInhouse Products/FundsJV Product 1JV Product 2VenueSales Manager2nd Manager (JP, KMcI SS)Inv. ManagerFilenote IssuedWorking DayNo. of AttendeesCancelledPack RequiredCompliance Required DivisionReason for no Compliance"Date sent to Compliance""Pack/Literature Used"Pack Path.
i want to use the Dsum to produce the total amount of managers met for a cvertain sales person for a certain type of meeting. I trued thr following but it is returninga #value =DSUM(A27:V1028,13,AND(3=$BH3,9=BI$2,12<>0)). where 13 applies to the number of managers column 3 is the meeting type 9 is the managers name 12 ensures that its only counted if teh meeting note is done.
I'm trying to do a DSUM based on a unique reference number. As I have 500+ such numbers, I need to automate the criteria array based on the reference number.
CSAMeeting DateMeeting typeCompanyInhouse Products/FundsJV Product 1JV Product 2VenueSales Manager2nd Manager (JP, KMcI SS)Inv. ManagerFilenote IssuedWorking DayNo. of AttendeesCancelledPack RequiredCompliance Required DivisionReason for no ComplianceDate sent to CompliancePack/Literature UsedPack Path
i want to use the Dsum to produce the total amount of managers met for a cvertain sales person for a certain type of meeting. I trued thr following but it is returninga #value
=DSUM(A27:V1028,13,AND(3=$BH3,9=BI$2,120))
where 13 applies to the number of managers column 3 is the meeting type 9 is the managers name 12 ensures that its only counted if teh meeting note is done.
I have a DSUM function with multiple criteria that are linked to user selections on a different sheet. If the user doesn't set a value for a particular field, the cell is blank and the DSUM should sum records with both blanks and non-blanks in that field.
Unfortunately, simply setting my criteria formula equal to that cell does not accomplish this. I've implemented an if statement to help but am having trouble defining a single criteria for returning everything.
Simply pointing to named range doesn't work for blanks and nonblanks when the cell is empty because it returns a zero: =SelectedValue
Criteria formula #1 I'm using: =if(SelectedValue="",="=*",SelectedValue) Criteria formula #2 I'm using: =if(SelectedValue="",="<>*",SelectedValue)
When the SelectedValue cell is blank, these two formulas ensure that all records are summed. I would like a criteria that sums both blanks (=*) and non-blanks (<>*) without having to use two separate cells for the criteria. This gets unwieldy when trying to combine it with criteria for other filter fields.
Is it possible to use a non-contiguous range for the criteria parameter of the DSUM function? I want to do something like this. =DCOUNTA(wholesale_all,"Assignment Date",D2:D3,G2:G3) This formulas won't work the way I have it typed, but is there a workaround to achieve the same result?
User# User_Christian User_Surname Role 1 bob smith operator 1 bob smith supervisor 1 bob smith warehouseman 2 john stone operator 2 john stone gunman 5 george brown Foreman
What Im after is:- a matrix with unique Role list across the top Unique ID & Name down the side (I can do all the above using recorded macro's) but the clever bit I cant even start to work out is how do I get matrix populated from the list?
I have attached a very simple model of a much larger BI report that we use. I have written a DSUM that returns the correct result in all cases other than when one of the criteria columns is blank. When one or more columns is blank, the result returned is 0 whereas I need it return all data (for e.g. if you remove "sains" from cell B2, I need it to still return data for person "b", "c" and "d" (i.e. 51 for Mar14)).
So I am trying to rearrange some cells on a report based of some criteria using an IF formula and getting Blank cells. Here's what I have:
LastRow = Range("A" & Rows.Count).End(xlUp) With Range("P2:P" & LastRow) Formula = "=IF($F2=""/FEDERAL EXCISE TAX"",$I2,"""")" .Value = .Value
[Code].....
So I just want it to identify whats in a cell and populate a related value if the text is present. Do I need to change formatting to find Tex or something along those lines?
I've got a forecast from a customer and need to summarize it with part number, quantity and date.
The spreadsheet is part no in column a due dates in row 1 values at the intersection of part no and due date and i don't want 0 quantity to report back.
SUM formula. My spreadsheet has roughly 8000 rows of data listing client info for how many commercials were purchased on Cable Networks a (a simplified example)...
What I am attempting to do is on Sheet2 summarize the commercials sold based on a starting Priority. So if the priority code in Column D is 350, I need to sum all spot sold at a priority of 350 and higher for each day-part and Network
ABCD1NetworkDay-partPrioritySold2HGTVPrime350 and up ?3HGTVPrime400 and up ?4HGTVPrime450 and up ?5HGTVPrime550 and up ?6HGTVPrime600 and up ?7HGTVPrime650 ?
I've attempted to create helper columns to tie all the necessary PR codes together and then SUMIF by the different helper columns, but I'm not getting the correct result. Instead of getting "15" for Sheet2, D2... I'm just getting "3"
I added an attachment with an example. You will see a matrix, with in the most left column an ID, then the headers of the matrix has different numbers that mean something.
What I want to do is, you fill in a number in the combobox, then press on a button and a report will be made on a new worksheet. With a list of the numbers and dates in the matrix that the ID has. Like shown in the example sheet in the workbook.
I come across a spreadsheet that is using DSUM formula, I did not understand why someone used DSUM instead of SUMIF formula, because using DSUM formula they had to use two extra cells for criteria, as seen in this example. I like to know is there any advantage of using DSUM instead of SUMIF in this example. The formule they used is =DSUM(J109:K113,2,$M$2:$M$3) and the criteria is G,D so they are adding all Debits in that column. I changed the formula to =SUMIF($J$110:$J$113,"D",$K$110:$K$113) that of course does not need $M$2:$M$3 range criteria.
G 3458224.37 D 3401317.50 C 3401317.50 C 56906.87 D 56906.87
I am attempting to have cells in Column 'U' deliver different drop-down menus based on the corresponding value in column 'D'. I have created 7 named lists:
Those lists will be called up based on 7 values in column “D”: “G” “152” “J” “X” “D/E” “V” “R”
So far I have only been able to get this to work for the first category “G”. When I change the value of column “D” from “G” to “152” I no longer get a drop-down. Here is the formula I am using in the List function of validation.
There are 11,000 rows and 4,000 are unique. The goal is to merge the data down to the 4,000 records. Each of the unique entries shows up 1 to 15 times.
In trying to solve this, I wonder if I should break this down into the different # of occurances and implement specific solutions. e.g., There are
5700 entries that show up 2x 504 that show up 3x 24 that show up 12x
I want to call a macro with a varying name that is within a module with the same name.
I have a module called Test1 and within this, a macro name called test1 I have a module called Test2 and within this, a macro name called test2 etc
On another module called Test8 (with the macro called Test8), this Test8 macro will call either Test1 or Test2 or Test3 etc depending on what I choose in an excel spreadsheet. So on sheet1, cell A1, there is a drop down with the options Test1 or Test2 or Test3 etc.
The following works to run the macro test1 from module test1 (when it does not vary i.e. i physically put in the name of the macro myself):
Sub Test3() Test1.Test1 End Sub
The following works to run the macro test2 from module test2 (when it does not vary i.e. i physically put in the name of the macro myself):
Sub Test3() Test2.Test2 End Sub etc
However, if I try it so that the calling of the macro varies as below , it does not work:
Sub Test3() MacroToCall = Sheets("Sheet1").Range("A1").Value MacroToCall.MacroToCall End Sub
Currently I have two tabs one with six columns of data. On a separate tab I have a matrix of two of the criteria. I would like to map the company data to the matrix, but recognizing the primary representative has more than one company he/she manages. Hence I don't just need the first entry, but all of his/hers maps to the size columns.
I am trying to identify some combination of formulas or functions that will allow me to adjust the cell reference within a function based on a value in another cell. For example, I have a series of values listed from cell C50 to cell W50. I want to use a function to calculate the internal rate of return over a range of the cells: =IRR(C50:W50) So far, so good.
However, I want the size of the range to be determined by a value entered in another cell, let's say B2. If the value in cell B2 is "5", I would want the formula to consider only the first five columns: =IRR(C50:G50). If the value in cell B2 is "10", I would want the formula to consider only the first ten columns: =IRR(C50:L50).
"One" represents the total that needs to be paid and "Two" represents the payments.
What I want to do is recognise when "One" is present and deduct "two" from that amount, but the whole spreadsheet is never the same (the data is being pasted from somewhere else) so it's really, really difficult to do that.
So I want to do =1000-500 (for that period, so in D2 it should say 500) and then =1500-400-300 (for the other period so in D5 it should say 800).
NB: Any payment ("two") that comes in after the 17th of the month will be carried forward to the next months "one".
Right to the point. I got two sheets in my file, one named "Order" and one named "Input". In the input sheet there is costs & revenues divided into several different divisions/activities. In column A I got the name of the cost/revenue and then the value for every period Jan-Dec in columns B-K. And the name of the activity is in column A under the cost/revenus for that activity, like a " SUM" row. Now, in the the "order" sheet, you can select a specific activity from a rolllist and then I want that activity's revenues & cost to be shown. I've attached the file to make it easier for you guys to understand my needs. I think I need some sort of 2 criteria VLOOKUP, that is first search fr the correct division, once this is found search for the different costs & revenues and the return a value. But I'm not so good at this so I can't get it to work. Maybe the INDEX function is good here but I don't get that at all. I tried Daves "2 criteria Vlookup" but all I get is #ERROR.
I have a large list of items (up to 3000) that are listed by category, description, part number and weight in columns C-F. Column A is where a user will enter a quantity in the rows of the items that they want. Once the user is done picking their items they hit a button that I have written a macro in, to copy and paste only the rows that contain a quantity in, on to another sheet. My problem is that for these items (that contain a quantity) I need to have a line number (in column B) that starts at 1 and counts up for each line item. Issues to consider:
-Line item 1 may not always be the top item. (because the user may not choose the top item) -There will be spaces inbetween the line items since the user may not pick all of their chosen items in order. -Users can pick an item at the bottom then pick one above it. (The Line item numbers will need to readjust to include the new number in the middle)
This has been bugging for a few days now cause whenever I think I finally get it I keep creating circular references
I have a 7-column 'Task' list that I need to query, and extract (preferably to a separate sheet) only those tasks that start 'ON' or 'BEFORE' the queried Date (or date range), AND/OR end 'ON' or 'AFTER' that date.
Column 1 = Dates Columns 2-4 = Task IDs (ID1, ID2, ID3 - must match as a group) Column 5 = indicates either Start or End of Task ('S' or 'E') (each Task has 2 such listings - a Start [s] and an End [E]) columns 6 & 7 = Misc. & Notes (unimportant as identifiers)
So, if a Task (identified collectively by col. 2-4) starts On or Before and ends On or After (col 5) the date (col 1) queried, then that Task should be included in the results list. The tricky thing is that a task that starts long before the queried date and/or ends long after the queried date needs to be included in the results list - therefore 'S' and 'E' (Start/End, col 5) and the 3 Task ID (col. 2-4) must be used along with the date for the query. And, if a task starts (or ends) on the queried date, then its counterpart (S/E) should also be included in the results list (if available).
I am trying out a new spreadsheet to log my cycling. I have attached the example so far. I am going to have a form appear and fill it out. Upon clicking 'Add' button I want a macro that will search column B (a list of dates) and match the date the users filled out on the form with the spreadsheet and then paste the corresponding form data in that row.
How would I accomplish this? Does the formatting of the date field complicate this since on the spreadsheet I display "Saturday 1/08" and on the form the user enters in "01/08/2008"
