Dsum On Multiple Criteria
Dec 15, 2008
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.
View 4 Replies
ADVERTISEMENT
Dec 15, 2008
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 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.
View 9 Replies
View Related
May 22, 2014
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)).
Attached File : DSUM (2).xlsx‎
View 3 Replies
View Related
Mar 11, 2009
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.
View 9 Replies
View Related
Sep 20, 2006
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.
View 8 Replies
View Related
Jan 5, 2009
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.
View 4 Replies
View Related
Nov 29, 2007
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.
This is as close as I've come:
DSUM('Income'!$A$1:$Q$37735,"US FREE AMT", LOOKUP(A2,Criteria!$A$1:$BX$2,OFFSET(Criteria!A1:BX2,-1,1,2,2)))
LOOKUP, of course, wants to return a value instead of a range, so this only works as far as getting me to the right cell on my criteria sheet.
How can I get a range 2 rows deep by 2 columns high from the results of a lookup/search?
View 4 Replies
View Related
Jun 1, 2007
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.
View 9 Replies
View Related
Dec 28, 2007
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?
View 2 Replies
View Related
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).
View 9 Replies
View Related
Feb 27, 2009
I would like to ask recommendations whether to use DSum or Vlookup, in order to do multiple search.
We receive a flat file on a daily basis, which contains four (4) columns. They are Group, Team, Taskname, and Number_of_Employees. The first three columns have text properties and the last one has number property. We copy this sheet into our workbook. There are more than 1,000 rows. I name this tab, "TotalWorkersbyTask".
Each group (first column) has its own tab. We try our best to name each tab to match the name of the first column.
We would like to find the unique value, in order to put the correct Number_Of_Employees into the correct column on each sheet. However, the relationship per column is many to many, which makes it more difficult.
For example,
Group,Team, TaskName, Number_of_Employees
12GR, Robert, Analyze raw data, 2.5
12OV, Tim, Analyze raw data, 1.25
12OV, Bob, Prepare reports, .85
16CJ, Jim, Prepare reports, .50
1008, Mary, Analyze raw data, .25
............
Each tab, such as 12GR, will have the task name on the line "Analyze raw Data" which matches the information "TotalWorkersbyTask" tab.
The problem is, if I do a Vlookup for Taskname only, only the first value of 2.5 will return in to the Number_of_Employees column.
For the tab of group 12GR, the logic supposes to be -> if the group is 12GR, and the name of the task equal to the one appeared in "TotalWorkersbyTask", return me the value of 2.5 in the specific row. With the same logic, on the tab 12OV, the same task name should return me the value of 1.25 as well.
As you see from the example, line 2 and line 3 are the tasks in the same tab, from the flat file received.
Then, this formula can be copied and populated into other rows and other sheets as well.
*********************
Questions:
1. Is there anyway to write the formula to gather the required information? My thought was using the Vlookup. However, it can see only one column.
2. Do I need to rename all the tabs to match the name of the first column received from flat file in order to make it more efficient? (In the present, there are some tabs that match the name, such as 12OV, and some tabs are something like ->Engineering, Research, etc.)
View 9 Replies
View Related
Nov 17, 2006
I'm trying to keep a running total (in one cell) for a range of cells
(see below).
The cells in the range consist of two columns- account number and an
amount. Given that the account numbers range from those beginning with
2XXX, 3XXX and 4XXX. The running total should be derived from the
totals associated to accounts beginning with 2XXX and 4XXX.
First attempt using pivot tables doesn't work since the amount column
is dynamic and thus is changed by user input - pivot tables would need
to be refreshed regularly which isn't possible. My goal is to have
this running total also be dynamic and performed automatically.
Second attempt using SUMIf doesn't work because the 'criteria' also
changes and isn't statis; the criteria in this case would be moving
with each row of data.
Third attempt using DSUM doesn't work b/c I can't seem to get the
syntax correct.
Account Original Budget
535030 24,600.00
535030 -
535036 36,900.00
535037 -
247695 -
258804 17,936.10 ..........
View 9 Replies
View Related
Nov 10, 2003
I am trying to convert the following Lotus .123 function into an excel function:
@IF(@DSUM($QUERY,"TOTAL EST LBS",(((TEXT WEIGHT=50)#OR#(TEXT WEIGHT=50))#AND#GRADE NBR=20#AND#@MONTH(MFG MTH)
View 9 Replies
View Related
Apr 14, 2009
I an having trouble with DSUM, the result I get is 0. I have a Journal Entry and I need to total the amount for a particular range date.
For the header I have:
Date|Account|Debit|Credit|Total|Account Name|Description
This is located from A5:G21
For A1:C2 I have Account|Date Start|Date End
For both Date I used the formula =">="&TEXT(E2,"mm/dd/yyyy") and the same for the ending date which is ="
View 9 Replies
View Related
May 2, 2007
I am using the SUMIF formula to sum all the cells that meet a certain criteria and at the moment, this works fine. Now I want to extend the criteria to 2(3 later) conditions. I've read that the SUMIF function only handles 1 criteria. Is this correct? If so should I use SUMPRODUCT or DSUM? I eventually will have to check about 2000 cells in a workbook. Sample work book attached: where I need to total the price of all the items that are shoes and are red in colour. A pivot table beckons in the future, but for now I need this in a workbook format.
View 3 Replies
View Related
Jul 17, 2007
What would be the correct syntax to convert this sumproduct formula to a dsum formula
I can't seem to find any examples that match my exact need
=SUMPRODUCT((Data!$A$2:$A$15998=$B$3)*(Data!$C$2:$C$15998=$A14)*(Data!$E$2:$E$15998=B$10)*(Data!$D$2:$D$15998))
View 7 Replies
View Related
Oct 21, 2006
I am trying to solve a problem. I am currently using this formula
= SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)
This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.
So the info looks like this in excel
MARKETING 200612 -10
MARKETING 200701 -25
MARKETING 200708 -50
ECONOMICS 200709 -30
The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
View 5 Replies
View Related
Dec 27, 2013
I'm placing dsum on a table, and want to sum some criteria I've wrote, all I need is the sum of the 15 latest match of the event I've wrote - which needs to be Sum together , I can use "helping column" in the table, but is there is more elegant way to do this with a furmula which make dsum calculate only the latest 15 cases ?
View 1 Replies
View Related
Oct 6, 2008
I've been using the DSUM function to pull in data from another worksheet.
In brief, the problem is that when I Cut & Paste the formula from C7 to C8, I want the Column reference to change but not the Row reference.
If I copy across the Columns then the result is OK – e.g. The Formula will change from…
#=DSUM([HBAP_SHUTDOWNS_2008.xls]NWM_SuccessfulShutdownsByDepartment!$A$4:$E$2924,"Total",'Calculations Page'!A$17:A$18)#
To…
#=DSUM([HBAP_SHUTDOWNS_2008.xls]NWM_SuccessfulShutdownsByDepartment!$A$4:$E$2924,"Total",'Calculations Page'!B$17:B$18)#.........................
View 4 Replies
View Related
Mar 24, 2014
I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.
View 1 Replies
View Related
May 18, 2014
Macro for AverageIFS, with multiple criteria in the same criteria range....
View 9 Replies
View Related
Jul 30, 2008
I have a spreadsheet which lists letters issued, the date issued and the potcode. I need a formula that counts, on a weekly basis, the number of letters issued to postcodes in Sutton and Bexley.
I have a count sheet set up on one worksheet and the list of postcodes applicable to each area are listed on another worksheet. I have been playing around with sumif, sumproduct etc, but these don't seem to work as I am pointing the formula to a list of postcodes and not an individual postcode. For example, the formula for one week needs to tell me, the number of letters issed to Sutton between 26/07/08 - 01/08/08. This is what I tried -
=SUMPRODUCT(('MU63 NC'!$F$2:$F$10>=COUNT!B$2)*('MU63 NC'!$F$2:$F$10<=COUNT!B$3)*('MU63 NC'!$D$2:$D$10=Sutton!$A$2:$A$50))
But I get #N/A - if I change the last refernce to a specific cell instead of a range it works, but this will make the process very lengthy as there are lots of postcodes!
View 5 Replies
View Related
Mar 18, 2013
I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.
I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).
The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.
As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.
The log code in column "H" Is based on this criteria:
First Letter of the Unique ID in column "E" - O, M, or L
Program Type in Column "F" - U or R
1. O-U = U
2. O-R = RU
3. M-U = U2
4. M-R = R2U
5. L-R = R
You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.
This is the area where my skill at using scripting dictionaries fails.
The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words
if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")
So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)
Here is my code so far.
VB:
Option Explicit
Sub test()
Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long
Set dic = CreateObject("Scripting.Dictionary")
[Code].....
View 1 Replies
View Related
Aug 5, 2012
I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$R1"),2*(AND("'"&$H$1:$H$43&"'!$E1">"'"&$H$1:$H$43&"'!$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$S1"),2*(AND("'"&$H$1:$H$43&"'!$G1">"'"&$H$1:$H$43&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$T1"),2*(AND("'"&$H$1:$H$43&"'!$I1">"'"&$H$1:$H$43&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$U1"),2*(AND("'"&$H$1:$H$43&"'!$K1">"'"&$H$1:$H$43&"'!$L1"))))
but it returns a value of zero each time. Clearly there is an error in the formula.
Here is some background:
-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.
View 1 Replies
View Related
Aug 31, 2012
I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
TABLE 1:
Project
Benefit Type
Delivered or Enabled
Benefit
PJ1
Financial
Delivered
Saving of $4M over 24 months.
[code]....
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
TABLE 2:
Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled
[code]....
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
Continued maintenance of hardware.
Increased capacity.
View 1 Replies
View Related
Jun 29, 2014
I have a directory which contains many files, they are all names based on their locations. eg. Burwood-File1.xls,Burwood-File2.xls,Burwood-File3.xls etc
I have a master that which will contain the branch in the first column.
I have defined the directory location in a separate sheet as well as email template.
When I click on the Send Email button I want ti to attach the files that match the Branch name.
The Branch list will constantly grow.
View 2 Replies
View Related
Feb 24, 2014
Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.
In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.
CURRENT SHEET
ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89PENS ABC STATIONERY CO LLC [code]....
The attached example file may be a better alternative to view this problem.
View 2 Replies
View Related
Feb 1, 2012
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
View 3 Replies
View Related
Apr 28, 2012
I am having such a difficult time creating a macro that will reduce the 5+hours I have to spend each week manually copying & pasting all of this data. I making an IMMENSE difference in this worker bee's life!
I have a workbook with two sheets (Sheet1 & Sheet2). Sheet1 has license #'s in column A and the state that the license belongs to in column B like this:
COLUMN ACOLUMN B11111Alaska11112Alabama11113Arkansas11114Arkansas
Sheet2 has three columns. Column A has the license #'s, column B has the state that the license belongs to and Columns C shows a line-of-authority tied to that license #.
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty11111AlaskaCasualty11112AlaskaLife11112AlaskaHealth11112
AlabamaProperty11112AlabamaCasualty11113ArkansasLife11113ArkansasHealth11114
ArkansasLife11114ArkansasHealth12345ArizonaProperty
I'm trying to write a macro that will compare the license # and state in Sheet1 to the license # and state in Sheet2. If it matches, append the contents of Column C to the corresponding row in Sheet1.
Here's the thing...Sheet2 contains the entries for all licenses in the company (so this table is HUGE). And there are multiple entries for each state license # (notice how there's two entries above for AK license # 11111 - one for the Property line and one for the Casualty line.
After my macro is run, I want Sheet1 to show all the lines-of-authority on a single line. So if I ran my macro on the above example, after it's run I would have this in Sheet1:
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty Casualty11112AlabamaProperty Casualty11113
ArkansasLife Health11114ArkansasLife Health
View 5 Replies
View Related
Feb 9, 2014
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
View 12 Replies
View Related
