Solved

Modification of VBA from SUMIF to SUMPRODUCT

Posted on 2015-01-15
9
174 Views
Last Modified: 2015-01-15
Hello,

here is a nice piece of code written by Jon http://www.excelcampus.com/vba/convert-pivot-table-to-sumifs-formulas-vba-macro/

basically it converts the pivot table into SUMIFS formula.   while this works perfectly, i was wondering how this code could be modified to produce formulas of SUMPRODUCT instead of SUMIFS.

thanks.
0
Comment
Question by:Flora
  • 4
  • 4
9 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40551120
Are we supposed to go thru the article and find out how to download the code and then work on it to try to di-mistify this into SUMPRODUCT ???

Better if you post the code within a workbook that have data ... so we at least understand what it is all about and then we can look it up to see how to get you the SUMPRODUCT.

gowflow
0
 
LVL 5

Author Comment

by:Flora
ID: 40551157
thanks gowflow. i am always truly impressed with extra ordinary solutions that you provide in this forum.


i have attached two workbook.

DATAwithPIVOT.xlsm  this workbook, click on pivottable and then run the macro called Convert_Pivot_to_Formulas
then a new sheet will be automatically created and then you can see that macro generates array formula.

DATAwithPIVOT2.xlsm this workbook follow the same click on pivot table and then run macro Convert_Pivot_to_Formulas
will generate a new sheet with SUMIFs formula but it is not array formula.

these two different pivot table converted formula that gets generates with single macro, it could be far better that if SUMPRODUCT could be generated, to avoid any mistake of control+shift+enter, in case if anyone clicks on a cell and forgets to presss the cnt shft entr.

thank you very much gowflow.
DATAwithPIVOT.xlsm
DATAwithPIVOT2.xlsm
0
 
LVL 29

Accepted Solution

by:
gowflow earned 300 total points
ID: 40551214
Pls note that a solution with SUMPRODUCT will not eliminate 'control+shift+enter' as this means that the solution is an array solution. So if your intent is to go via SUMPRODUCT to avoid the 'control+shift+enter' then I think your on a wrong path !

gowflow
0
 
LVL 5

Author Comment

by:Flora
ID: 40551227
thanks gowflow,  at least if i can get SUMPRODUCT instead of SUMIFS that should be okay too.  even if it would still require the keystroke of controlshiftenter.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 29

Expert Comment

by:gowflow
ID: 40551262
Why do you need SUMPRODUCT ?
0
 
LVL 5

Author Comment

by:Flora
ID: 40551361
becuase for users it is confusing having SUM(SUMIFS   instead of both, would it be possible to have one SUMPRODUCT?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40551926
Sorry but do not get it !!!

What kind of users do you have? is the result of the formulas correct ? does it give the correct values ??? If yes then what does it matter if the formula is 1 line or several or if it is SUM of SUM or ... ??

I do not understand.

gowflow
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 200 total points
ID: 40551933
Flora,  I find that SUMIFS is actually more intuitive than SUMPRODUCT and doesn't require any numerical conversion of logical results (ex. --(A2="x") ).  Also, the SUMIFS function processes faster than equivalent SUMPRODUCT function.  See this ExcelUser blog post that describes this.

Regards,
-Glenn
0
 
LVL 5

Author Comment

by:Flora
ID: 40552043
thank you gowflow and Glenn.

SUMIFS give correct result, so I will stick with that.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now