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