DSUM Adjacent Criteria
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
ADVERTISEMENT
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
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
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
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
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
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
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
Apr 5, 2014
What I would like to do is return the total qty of items on the 'QUOTATION' tab that are upholstery and this having been determined by whether there is a Y (for Yes) or N (for No) against the equivalent item in the 'BASIC_LIST' tab.
So in the below example, the total of Upholstered items would be 182 as items 'ST-11' to 'ST-13A - Tray' do not have a 'Y' against the matching item references in the 'BASIC_LIST' tab.
I would like this Y/N column to be the determining factor as opposed to any other criteria or filtering. (Also the Items column in the 'QUOTATION' tab is only ordered in the way it is for ease of view for this query!)
Picture 2.png Picture 3.png
SUM VALUES OF ITEMS THAT HAVE ALREADY MATCHED ANOTHER CRITERIA.xlsx
View 4 Replies
View Related
Aug 1, 2012
I need a code or macro which, when applied to a selected column, will merge a range of cells in that column into one cell. The criteria is to merge all cells into one cell that fall directly under the cell which contains "Items - total..." and that lines are separated by a line break (alt+enter).
Here's an example of what the column contains:
Before code:
A1: Items - total: 2
A2: four-legged
A3: cat
A4: two-legged
A5: chicken
A6: Items - total: 1
A7: four-legged
A8: dog
A9: Items - total: 3
A10: four-legged
A11: pig
A12: two-legged
A13: eagle
A14: marine animal
A15: shark
After code:
A1: Items - total: 2
four-legged
cat
two-legged
chicken
[Code] .......
View 1 Replies
View Related
Sep 2, 2009
What I am attempting to do is find the MIN value in Column C where values in Column A are equal.
The data would look like this
A B C D (D:D is where the "MIN" Formula will be)
Scope1 NameA $100
Scope1 NameB $145
Scope1 NameC $115 $100 (I want the min value to show up here)
- (this would trigger a break between scopes, and provide a conditional format separator)
Scope2 NameE $450
Scope2 NameG $345
Scope2 NameX $415 $345
-
So every time I put a "-" I would like the MIN formula to trigger in (Row#-1,D)
View 5 Replies
View Related
Apr 28, 2013
I need to return the values in Column A IF any of the adjacent cells (columns) contain a number.
Sample data and expected Results...
Sheet2 Â ABCDEFGHIJK1DateData1Data2Â Data3Data4Â Data5Â ResultÂ
201/01/20132Â Â Â Â Â Â Â 01/01/2013Â 302/01/201311Â Â Â Â Â Â 02/01/2013Â 403/01/2013Â 1Â Â Â Â Â Â 03/01/2013Â 504/01/2013Â Â Â
13Â Â Â 04/01/2013Â 605/01/2013 06/01/2013Â 706/01/2013Â Â Â Â
1Â Â Â 08/01/2013Â 807/01/2013Â Â Â Â Â Â Â Â 11/01/2013Â 908/01/2013Â Â Â Â
1Â Â Â 12/01/2013Â 1009/01/2013Â 13/01/2013Â 1110/01/2013Â Â Â Â Â Â Â Â 15/01/2013Â 1211/01/2013Â Â Â Â Â Â 2Â Â Â 1312/01/2013Â Â Â Â Â Â
1Â Â Â 1413/01/2013Â 1Â 1Â Â Â Â Â Â 1514/01/2013Â Â Â Â Â Â Â Â Â Â 1615/01/2013Â Â Â 3Â Â Â Â Â Â 17
I cannot use VBA, Advanced Filter or a Helper column, but I could use one additional cell to hold a count, so, the solution can only be a formula.
View 7 Replies
View Related
Jun 23, 2008
I want to count no of "yes" in Range Column B only if corresponding values in column A is less than 10/06/2008
Please see the attached file for refrence
View 5 Replies
View Related
Apr 30, 2014
In the picture below, I need a formula in column E to count the unique occurrences in column A (excluding blanks) if its corresponding value in column B (B1 value) matches that in column D (B2 value). Currently column E is showing the values I would want the formula to return.
Capture.PNG
At the moment I have a formula as below:
=SUMPRODUCT(--(B:B=$D2),--(A:A<>""))
this will do a countif in column A if column B matches the value in column D, but would not weed out duplicates for me.
Modifying my formula. I have attached the sample workbook below.
Book1.xlsx
View 7 Replies
View Related
Jun 11, 2014
I have developed the following code to colour cells dependent on reference to adjacent criteria (in Col D).
Is there anyway I can simplify this code to avoid three separate "For each" statements?
Code:
For Each c In Range("E12", Range("e" & lastrow))
If c = "" And c.Offset(, -1) "" Then
c.Interior.ColorIndex = 7
[Code] ..........
View 2 Replies
View Related
Aug 13, 2008
I have a have excel problem Im trying to figure out. I have 2 sheets. Sheet1 and Sheet2. I want to copy into sheet1 cell B2 the contents of sheet2 column C row x iff sheet2 column B row x = 1. There will be only one cell in that column equal to 1, so I will be copying that cells(colB; rowx) adjacent cell (colC;rowx) contents. This is what I have so far but it doesnt work. =IF('Sheet2'!B:B=1,DONTKNOW WHAT TO PUT,""))
View 5 Replies
View Related
Jun 1, 2007
I am trying to use the DCOUNT function to count based on two different criteria in adjacent columns is this applicable.
Situation:
I have column B that contains initials and I have column C that contains a type (of store) so it'd look like this
jp RET
jp RTO
jb RET
ma RTO
Rn CI
ma Fur
tc CI
ma RET
jp RET
and I want to have a count of how many RET's there are for Initials JP ect
View 6 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
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
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
Jan 5, 2014
I have a workbook with 30 worksheets. Each sheet has 84 rows of data (start in 15 columns (A to O). I would like to create a summary sheet that only shows the most important data from each sheet.
The summary sheet would have 12 lines of headers and formatted crap at the top.
The Summary sheet header columns would be:
Site (A), Date (B) Health (C), Status (D), Critical (E), Task (F),
Dependencies (G), Owner (H), T-Date (I), Task Date (J), Mitigation Date (I)
The data in the sheets are not in that order, of course.
That
1. puts the name of the sheet I am copying from in column A
2. the deadline date in Column B (that date is always in C10 of each worksheet)
3. and copies Cells from Column A,B,G,H,I,O in any row in which the value in A is not "good" into columns C through H. I would like to paste those rows into the summary sheet. I have code that loops through the sheets and rows in each sheet to find the rows to copy. I can copy cell values directly from the active sheet to the summary sheet, but because I am copying a cell at a time, it takes 7 minutes. Yes I am impatient :)
Here is the code snippet where the copying is done:
Dim sh As Worksheet 'current worksheet
Dim DestSh As Worksheet 'worksheet in which to paste summary
Dim Last As Long
Dim CopyRow As Long 'row to copy
Dim LastCopyRow As Long
[Code] ........
ExitTheSub:
Application.Goto Sheets("KMARollup").Cells(1)
End Sub
I think there must be a way to use ranges to build an array of cell values and paste only once but I am lost here.
View 2 Replies
View Related
Jan 13, 2008
I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...
View 12 Replies
View Related
Dec 19, 2006
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
View 9 Replies
View Related
