Sum Product with month selection from combobox

  • Dear Sir


    I have the following VBA formula


    Code
    "=SUMPRODUCT(--(data!R5C1:R" & lngEndRow & "C1=RC3)*--(MONTH(data!R5C2:R" & lngEndRow & "C2)=MONTH(9)))"


    I have a combobox in which all twelve months are mentioned in words (January, February, March etc)
    Is there a way in which the formula calculates the data pertaining to the month selected in the combobox so that I do not have to write 12 different formulas.

  • Re: Sum Product with month selection from combobox


    The VBA code is proper and shows on the formula bar correctly, but it is not picking up the data from the sheet
    For Eg. I have 3 items in the month of October, and accorting to the formula 3 should be displayed in another sheet, but that is not happening.
    According to the VBA formula that you have given the formula bar displayes as follows


    Code
    =SUMPRODUCT(--(Data!$A$5:$A$109=$C16)*--(MONTH(Data!$B$5:$B$109)=MONTH(10)))


    The value in the cell is displayed as "0"


    I changed he date format to dd/mm/yy and also mm/dd/yy but of no use.

  • Re: Sum Product with month selection from combobox


    Please see the attached sheet.
    One more I wanted to add that had been missed out by me in my earlier post is that
    As in the example sheet attached in Sheet2 for Eg. the particulars GJ-A-CX-7108 the value should appear how many times it is appearing in Mumbai (in the month selected from the combo box)
    Same for all
    The value is not appearing as it should come.

  • Re: Sum Product with month selection from combobox


    Hi Skamat


    Change your code as follows. Then it will work. But I just want to let you know that, the code you are using will work only if the year of all bills are in 2012. Otherwise you need to add one more condition to check the year also.



    Regards


    Maqbool

  • Re: Sum Product with month selection from combobox


    Sir it is working perfectly fine. As you mentioned I need one more condition so that I can check for both years 2012 and 2013.
    Also in my previous post as mentioned that I need 3 sumproduct conditions.
    If vehicle number matches the date and place the quantity should be displayed.

  • Re: Sum Product with month selection from combobox


    Sir it is working perfectly fine. As you mentioned I need one more condition so that I can check for both years 2012 and 2013.
    Also in my previous post as mentioned that I need 3 sumproduct conditions.
    If vehicle number matches the date and place the quantity should be displayed.

  • Re: Sum Product with month selection from combobox


    Quote from skamat;647402

    Sir it is working perfectly fine. As you mentioned I need one more condition so that I can check for both years 2012 and 2013.
    Also in my previous post as mentioned that I need 3 sumproduct conditions.
    If vehicle number matches the date and place the quantity should be displayed.


    That's a poor response! A PivotTable is faster & more efficient than VBA.


    I've just created a PivotTable using your data that is more accurate than the results you have in that workbook

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!