98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Dynamic ranges and Sumif formulas
Dynamic ranges and Sumif formulas
Resolved · High Priority · Version 2013
Fri 29 May 2015, 15:57Delegate Sam said...
Sam has attended:
Excel Dashboards for Business Intelligence course
Dynamic ranges and Sumif formulas
Hi,
i have the following formula that works great with data formatted as a table.
=IF(MyMonth="Grand Total", SUMIF(Table1[Name],$K13,Table1[Total No of Units]),SUMIFS(Table1[Total No of Units],Table1[Data Month],MyMonth,Table1[Name],$K13))
however i need to run the same formula for data from a Pivot table. I have created a dynamic range for the pivot table (inc a Counta so that it will auto increase as data is added) but i cant get the same formula to work?
can you advise what i need to do? i have tried the following (where DynSchool is the name for the dynamic range i have created), but it doesn't work -
=IF(MyMonth="Grand Total", SUMIF(Dynschool[School Name],$B15,dynschool[Qty]),SUMIFS(Dynschool[Qty],dynschool[Data Month],MyMonth,Dynschool[School Name],$K15))
Fri 29 May 2015, 16:34Trainer Jens said...
RE: dynamic ranges and Sumif formulas
Hi Sam,
Thank you for the forum question.
We are only using the [] in format as table tables. If you create dynamic range names in a pivot table you only need to refer to the range name.
I have attached a workbook where I have created an example. I hope this can help you.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
sumif pivot.xlsxFri 29 May 2015, 16:38Delegate Sam said...
RE: dynamic ranges and Sumif formulas
Hi Jens,
Thank you so much for the quick response.
I cant see any attached me on here - apologies but where should I be looking for it?
Sam
Fri 29 May 2015, 16:42Trainer Jens said...
RE: dynamic ranges and Sumif formulas
Hi Sam,
Sometimes it takes some minutes before you can see the attachment.
Please press F5 and have a look under my signature. You should be able to find the attachment here.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 29 May 2015, 17:03Delegate Sam said...
RE: dynamic ranges and Sumif formulas
Hi,
That's great thank you, guess i was just too impatient.
is there a way for me to upload or email a file if I am having trouble with a particular formula?
it was working, but then somehow I've lost it all and I cant see what I've done wrong so not sure how to ask for help on here?
Sam
Mon 1 Jun 2015, 09:20Trainer Jens said...
RE: dynamic ranges and Sumif formulas
Hi Sam,
If you attach the file to an email and send the email to info@stl-training.co.uk
Please add my name in the subject.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 1 Jun 2015, 13:59Delegate Sam said...
RE: dynamic ranges and Sumif formulas
Hi Jens,
That's great thank you.
Can i just confirm before i send it that the information will be kept confidential and not shared?
Kind regards Sam
Mon 1 Jun 2015, 14:44Trainer Jens said...
RE: dynamic ranges and Sumif formulas
Hi Sam,
Yes the file will be deleted after I have looked at it and information from the file will be kept confidential.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
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. |
Excel tip:Customize the toolbar in Excel 2010You can create your own toolbar which contains your favourite or most used tools. This will make using Excel much more efficient. To do this, you need to click on View, then select Customize Quick access Toolbars and then select Customize. A list of tools will then appear on the screen of which you can add or remove them as you please. |
