Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

Not understanding how a excel fromula caldulates

I have a excel formula that I need to transfer to a access formula but i dont understand the formula what it means or how it is calculating.

equavalent vehicle complete is the  Name of the field that it is calculating and this is the formula

=SUMIF($D$31:$D$395,"="&D4,$AE$31:$AE$395)


column d31- d395 are name of units like FY9 or fy10


column ae31-ae395 are percentages some are 100% SOME ARE 10 % and 5% AND SO ON
0
gigifarrow
Asked:
gigifarrow
  • 3
  • 2
  • 2
  • +2
4 Solutions
 
terencinoCommented:
The equivalent formula in Access is DSUM, have a look at this tutorial and let us know if you need any further help with that
MS Access: DSum Function

Essentially you must convert the Excel sheets and ranges to tables and fields, but otherwise the concept is exactly the same
...Terry
0
 
honestman31Commented:
source  http://office.microsoft.com/en-sg/excel-help/sumif-HP005209292.aspx

SUMIF   Adds the cells specified by a given criteria.
0
 
terencinoCommented:
So in Excel the SUMIF looks up in column D, picks out the cells that are the same as the criteria, eg FY10, then adds up the matching percentages on the same rows in Column AE.

Similarly say in Access you have a table called tblPercentages, it will have one field Year with data same as column D, and another field Percent with all the values from Column D. Then to get the same value as the SUMIF, your DSUM formula  would be:

=DSum("Percent", "tblPercentages", "Year = FY10")
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gigifarrowAuthor Commented:
Okay thanks experts but when i total them up it is not coming out correct. here is a example.The field is called Vehicle Equavalent I have it highlighted.

And the fields that are  supose to total i have them highlihted also.
exampleofvehicleequivalent.xlsx
0
 
barry houdiniCommented:
I don't see any formulas in that sheet - can you give a specific formula that you believe is mis-calculating?

If you are using the formula from your question, i.e.

=SUMIF($D$31:$D$395,"="&D4,$AE$31:$AE$395)

then because D4 is blank that will sum all the AE column values where the corresponding cell in column D is blank

regards, barry
0
 
Jeffrey CoachmanCommented:
Yes, please post a sample sheet that actually works for the formula you posted.

Then post a sample of the Access data and the exact results you want...

As was stated by terencino, the equivalent in Access would be Dsum.
The basic syntax is:
=Dsum("YourField", "YourTable", "YourCriteria")

For example:
=Dsum("Salary","tblSalaries","EmployeeID=" & me.txtEmployeeID)
This will sum all the salaries if the EmployeeID matches what is in the EmployeeID textbox
(Presumes EmployeeID is a number)

If you are dealing with a string criteria (like FY10), then the syntax becomes "roughly" something like this:
=Dsum("Cost","tblCosts","Unit=" & "'" & me.txtUnit & "'")


JeffCoachman
0
 
gigifarrowAuthor Commented:
Sorry about that. Been busy I had copied and pasted it and I didnt know that the formulas would not show up here is the formulas.

The field is called vehicle:equavalent vehicle complete
BUSK-III-FS3--STATUS-REPORTforhe.zip
0
 
Jeffrey CoachmanCommented:
So then the info we posted should help you build your equivalent Dsum() formula
0
 
terencinoCommented:
Hi gigifarrow it looks like your SUMIF works fine, why do you think it is not working?
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now