Modification of VBA from SUMIF to SUMPRODUCT
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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Why do you need SUMPRODUCT ?
ASKER
becuase for users it is confusing having SUM(SUMIFS instead of both, would it be possible to have one SUMPRODUCT?
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you gowflow and Glenn.
SUMIFS give correct result, so I will stick with that.
SUMIFS give correct result, so I will stick with that.
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