Forum home » Delegate support and help forum » Microsoft Excel Training and help » SUMIF/ DSUM
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
SUMIF/ DSUM
Resolved · High Priority · Version 2003
Reply Wed 30 Nov 2011, 11:17Delegate Brian said...
Brian has attended:
Excel Advanced course
SUMIF/ DSUM
How do i use sumif or dsum to get total quantities if asking for 2 different criteria e.g. I want to find total stock for a stock code 41303 and have stock type 001.
I have multiple codes and have 3 different stock types 001, 100 and 003 so using sumif at the moment brings up total quantity for 41303 including 001, 003 and 100 stock, i would like to get total quantity for articles in each stock type.
my stock type is in column B, Article code column E and qty column E. The data has multiple lines with same product and stock type so need to sum these up.
For upcoming training course dates see: Pricing & availability
RE: SUMIF/ DSUM
Hello Brian,
Hope you enjoyed your Microsoft Excel Advanced course with Best STL.
Thank you for your question regarding using either SUMIF / DSUM.
I put together a few numbers similar to what you described in your question. I used a criteria area which has drop down lists which make it easier to produce the data you require.
I have also added an auto filter which you can use to provide the same results. At the bottom of the total column I added a subtotal function which helps when working with autofilters.
Test what I have given you and then try it out on your data sheet.
I have attached the file for your use.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
Attached files...
DSUM.xlsReply Mon 5 Dec 2011, 08:59Delegate Brian said...
RE: SUMIF/ DSUM
I have used all formulas as described but my formula is still coming up with a error. Is there any way of me sending you the file to test on my data?
I can't see any way of sending file on here like you have with DSUM file.
Is there a limit of how many article codes you can use with DSUM?
RE: SUMIF/ DSUM
Hello Brian,
Please send your file to me at:
rl
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
Reply Mon 5 Dec 2011, 11:59Delegate Brian said...
RE: SUMIF/ DSUM
have sent email with attachment of data to sort.
Thanks for your help.
This is just bugging me and will be helpful if i can sum these up with dsum.
RE: SUMIF/ DSUM
Hello Brian,
Thanks for sending me the file. Your formula is incorrect as the criteria part does not include the headings. I have attached the file with a note. Remember, for Excel to provide a correct answer it must be able to match the headings from the criteria area to the database. A quick edit will solve this problem.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
Attached files...
|