Solved

Modification of VBA from SUMIF to SUMPRODUCT

Posted on 2015-01-15
9
197 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 30

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 6

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 30

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

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
 
LVL 30

Expert Comment

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

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 30

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 6

Author Comment

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

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

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.

752 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