Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

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.
0
Flora
Asked:
Flora
  • 4
  • 4
2 Solutions
 
gowflowCommented:
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
 
FloraAuthor Commented:
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
 
gowflowCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
FloraAuthor Commented:
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
 
gowflowCommented:
Why do you need SUMPRODUCT ?
0
 
FloraAuthor Commented:
becuase for users it is confusing having SUM(SUMIFS   instead of both, would it be possible to have one SUMPRODUCT?
0
 
gowflowCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
FloraAuthor Commented:
thank you gowflow and Glenn.

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

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now