Solved

Modification of VBA from SUMIF to SUMPRODUCT

Posted on 2015-01-15
9
185 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 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 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 29

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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EXCEL 2013 question. 4 29
Left trim cells in column A Excel vba 2 32
Select Next Route by Time 4 20
Saving excel document in lotus script 4 13
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

803 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