Dsum Or SumIf

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


ADVERTISEMENT

Sumif V Dsum V Sumproduct

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

DSUM Formula Instead SUMIF Formula

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

DSum Criteria ..

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

DSUM Function

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

Dsum For 2 Different Criteria

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

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 View Related

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 View Related

DSUM Date Ranges

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

Convert Sumproduct To DSUM

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

Dsum With Variable Criteria

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

Dsum Based 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 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

Not Sure To Use Dsum Or Vlookup For Multiple Searches

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

DSUM When Criteria Field Is Blank

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

Dsum With Non-contiguous Range Criteria

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

DSUM Multiple Criteria - Return Data

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

Write DSUM Which Calculate Only The Latest 15 Times?

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

DSUM Function To Pull In Data From Another Worksheet

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

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).

View 9 Replies View Related

Formula- To Pull Cell Values Similar To A SUMIF Function (SUMIF(range,criteria,sum_range))

Oct 25, 2007

I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.

View 9 Replies View Related

Nested SUMIF Statement Or Multiple SUMIF

Sep 17, 2009

I need to perform 2 SUMIF's on 2 columns of data to return a result and I'm not quite sure the best way of doing this. I'll give an example below.

I have 2 columns of data, both numeric and the SUMIF needs to say if H1:H100="10" and also if J1:J100="907". I can perform one or the other but not both.

View 6 Replies View Related

SUMIF In A Column: The First Instance Of Each SUMIF

Apr 21, 2009

I have many kitchens using the same recipes. I need to distill information down until I've got a summary of how much is being made. Uploaded is a condensed version of the point in the process I'm having difficulty with. This workbook will pull information from 8 other workbooks and give me excatly what everyone made on any weekday.

And from there, with the kind help of this forum, I figured out how to do a SUMIF based on the recipe number. And it summed up all instances of 'Recipe X' being used. However, it continues to SUMIF itself all the way down the page... which is good, because of how recipes are chosen for each kitchen. However, I only need to report one instance of each recipe.

In the uploaded example (and I apologize for the colorful sheet, but it helped me double check what I was working on.) ... I only need to report the PURPLE results elsewhere... the first instance of each SUMIF.

View 5 Replies View Related

SUMIF And SUMIF Not - Using Two Formulas In One Cell

Feb 27, 2012

I am wanting to use these two formulas in one cell. Is there anyway to do this? If "AD3" is 0 I want this =SUM(X3:AC3) and then if cell "AD3" is greater than 0 I want to basically use this formula

=SUM(AH3,X3:AC3)-AD3.

Is there anyway to merge these two formula's?

View 2 Replies View Related

Using SUMIF + AND (if Possible)

Jan 14, 2009

I'm working on a sheet where by I need to reference multiple columns of information to calculate a total.

Such as (simplified example of my real issue):

__A__B__C
1 20 AP1 C30
2 25 AP1 C30
3 35 AP2 E10
4 50 AP3 E10

My requrement is to only add values in column A wich have corresponding column B="AP1" AND column C="C30"

I've tried the following and keep getting errors:
=SUMIF(AND(C1:C4,"C30"),(B1:B4,"AP1")),A1:A4

View 5 Replies View Related

Sumif With Or

Oct 12, 2005

I'm trying to do this:

=SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)

It returns zero each time.

(BTW,
=SUMIF(B5:B12,"=D*",D5:D12)
and
=SUMIF(B5:B12,"=?D*",D5:D12)
both work just fine and return non zero sums.)

So my question is,
Where do I put the OR, or is there another way to do this?

View 14 Replies View Related

{SUM(IF(...} To SUMIF(...)

Apr 4, 2009

I have a workbook which I am trying to clean up and make more efficient (in terms of formula calculating time).

In it are examples of array formula such as this one (which work correctly):


{=SUM(IF(Expected!$A7:$A894=5,Expected!$K7:$O894))/3}

From past readings here, I have heard that the array {SUM(IF(...} is slower in calculating than the SUMIF(...) equivalent.

As such i tried to convert is to:


=SUMIF(Expected!$A7:$A894,5,Expected!$K7:$O894)/3

This gives a different answer though. I thought for a single criteria they would be equivalent.

Q1: What am I doing incorrectly above?

Q2: Is there a more efficient version of the array formula I could use (SUMIF and otherwise), so I can have some options availible.

View 9 Replies View Related

About Sumif And Mid

Oct 19, 2009

I got a column A containing a alpha-numeric data and want to sum up the numeric value of each cell which begins with "t" and have the sum divided by 486 (i.e. (120+230+25)/486). I tried the following formula but to no avail.

A
t120
t230
m45
m30
t25

My formular: sumif(mid(a1:a5,2,3,">0"))

View 9 Replies View Related

SUMIF Or VBA Or Both

Feb 18, 2006

I have worksheet of data. I have another worksheet that is used just for a summary. This data is mapped to a "Job number" (1123646, 1256974, etc.) The first two numbers determine what area it is referring to.

Each column represents a different amount (month to date cost, month to date profit, year to date cost, year to date proift, etc.) I need to summarize each column for each area and display the result on the other worksheet.

I can't use exact ranges like = sum(a9:a31) b/c that range will change each time the worksheet is used. I thought maybe using a loop, but then I got stuck and don't know where to go from here. Here is what I have so far.

Sub SumProfit()

Dim C As Range, sh1 As Object

Application. ScreenUpdating = False
Set sh1 = Sheets("JB-PRF")
Set C = Range("A9")

I don't want to abuse LOOPING or VBA for that matter, but I don't know of any other way to accomplish this seeing as how the ranges will not be the same each time. the shee that the data should be extracted to is named SUM-PRF

View 9 Replies View Related

Sumif

Jan 18, 2007

I am trying to sum the total of 10,000 lines in column B if column A is 0. I cannot get sumif to work. do I use a different formula?

View 6 Replies View Related

Converting Sumif To VBA

Nov 26, 2013

I know this is a dumb question but I can't get a sumif to work in vba. I want the actual formula in the cell, not just the output value.

This is my most recent attempt:

VB: Range("C2").Formula = "=SUMIF(Z7:Z12000, >0,N7:N12000)"

I get Run-time error 1004, application defined or object defined error.

Why doesn't this work?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved