• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct VBA Function

A

ashish mehra

Active Member
Hi VBA Champs,

I am uploading a sample file wherein I have used Sumproduct, Countifs Function to extract the output.

Requirement: I don't really know how to convert this into customized VBA function.
I want my end user to Click on the Macro button & generate the Report. I don't want my end user to see the Functions.

Regards,
AM:)
 

Attachments

  • Test for Excel Expert.xlsm
    734.4 KB · Views: 6
AM

Why not use Regular spreadsheet functions
Then protect the worksheet
When you protect the worksheet you have the option to hide worksheet formulas so that the user can't edit or even see the formulas
Apply a password so the users can't unlock it
 
Last edited:
Dear Hui Sir,

I want to learn the SUMPRODUCT VBA Function. My Boss doesn't want to see any formula. He is only interested in Clicking the Macro button & results should produce automatically.;)

Regards,
AM:)
 
I have attached the file

I ended up using the Countifs and Sumifs functions and you cannot see the formulas at all


Worksheet password is Chandoo


You will notice that I changed some of the Column Headers on the Answers sheet so that it matches the values in the Columns of Raw Data
I have used Named Formulas for the columns to make reading the formulas easier
 

Attachments

  • Test for Excel Expert IH.xlsm
    736.6 KB · Views: 4
Last edited:
Dear Hui,

First of all thanks for your prompt response & nice suggestion to use Protect sheet feature.

Let me clear out my requirement: I want to make dynamic worksheet (Raw Data) which will populate the output as & when data is entered.

Sir: There are 2 requirements.

1) By using Protect sheet feature: Macro is giving error when I unprotect & then run the Macro
Range("D5:G7").Formula = "=SUMPRODUCT(('Raw Data'!$D$2:$D$10380=Ashish!$W5)*('Raw Data'!$E$2:$E$10380=Ashish!W$3))"

Please help me out.

2) I specifically want to use Sumproduct & Sumifs VBA function.

I appreciate if you can help me in making customized VBA function.

Regards,
AM:)
 
@Ashis

i have add a extra code for protect and unprotect in the code of Hui

Please Download the file and inform us if any issue

Thanks

SP
 

Attachments

  • Test for Excel Expert IH.xlsm
    731.2 KB · Views: 4
The attached file below has replaced the fixed Ranges with Dynamic Named Formula
It will expand and contract as data is added to the Raw Data Sheet
 

Attachments

  • Test for Excel Expert IH.xlsm
    737.5 KB · Views: 8
Dear Patnaik & Hui Sir,

Thanks for Enhancement#1.

Please help me in Enhancement#2.
2) I specifically want to use Sumproduct & Sumifs VBA function.

Regards,
AM:)
 
I'm sorry AM, I won't help you with this
It is not an application that I would recommend you use VBA for

1. Your goals of hidden formulas can be achieved without VBA and by simply using Simple spreadshset formulas and Hiding cells formula Display
2. Adding VBA adds a level of sophistication and support that isn't warranted in this circumstance.
3. You risk users not enabling Macros and then not being able to report.

The above file satisfies all your criteria
 
Thanks Vijay,

You made my day!:):):):)

I was looking for the same kind of approach.

Just to addon, Can you please help me in making same report by using APPLICATION.WORKSHEETFUNCTION.SUMPRODUCT. I really wanna learn this.

Dear SM: Bosses are always like that they only accept what they want. Something similar to result will not make them happy!!!!!;)

Regards,
AM:)
 
You have a great sense of humor SM:)

By Boss is not cruel by nature. The idea is he want me to make template which will help my Branch Managers to calculate the overall Sales Figure by single CLICK of a button.

My idea to post the question is just to take a leaf out of Vijay Vizzu's book & apply it in my main Reports.

Regards,
AM:)
 
Using a Formula based approach you have the same solution without the click of a button !

Is this a School Assignment ?
 
You must log in or register to reply here.
Back
Top