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

SUMIFS using macro

ammo4814

New Member
Dears ,

could you please help me in the attached file i am trying to use sumifs function using macro vb code , attached the file iam working with .

Thanks
 

Attachments

  • Test Vb.xlsx
    12.9 KB · Views: 28
Is this...


Code:
Sub test()
    With Sheet2
        .[c7:c11] = "=SUMIFS('Date '!$D:$D,'Date '!$A:$A,$C$3,'Date '!$B:$B,$C$4,'Date '!$C:$C,Sheet2!B7)"
        .[c7:c11].Value = .[c7:c11].Value
    End With
End Sub
 
Try:

Code:
  Range("C7").FormulaR1C1 = _
  "=SUMIFS('Date '!C4,'Date '!C1,R3C3,'Date '!C2,R4C3,'Date '!C3,Sheet2!RC[-1])"
   
  Range("C8").FormulaR1C1 = _
  "=SUMIFS('Date '!C4,'Date '!C1,R3C3,'Date '!C2,R4C3,'Date '!C3,Sheet2!RC[-1])"
   
  Range("C9").FormulaR1C1 = _
  "=SUMIFS('Date '!C4,'Date '!C1,R3C3,'Date '!C2,R4C3,'Date '!C3,Sheet2!RC[-1])"
  Range("C10").FormulaR1C1 = _
  "=SUMIFS('Date '!C4,'Date '!C1,R3C3,'Date '!C2,R4C3,'Date '!C3,Sheet2!RC[-1])"
  Range("C11").FormulaR1C1 = _
  "=SUMIFS('Date '!C4,'Date '!C1,R3C3,'Date '!C2,R4C3,'Date '!C3,Sheet2!RC[-1])"
 
Deepak ,
thanks for your help , i have problem in implementing the code in my file , when i rum the macro give me open window then give me #VALUE! .
can you help me with this
 
It means you wrongly referred the sheetname.
Will you pls upload the xl where u getting the error so that we can trim it for you.
 
Dear ,
attached the file i have problem with . is the problem the sheet name "data " how i can solve it if i have the same problem in other sheets .
other thing could you help me with i add total so i want the code take the cells before the total and after the total and not take the total cell ,

Thanks alot for your help
 

Attachments

  • Test Vb.xlsm
    18.4 KB · Views: 22
Dear ,
i appreciate your help i have other thing what i have to add to my code to make the data change automaticly whrn change the name without nedd to run the macro each time i change the name .

Thanks
 
Deepak ,
thanks for your help , i have problem in implementing the code in my file , when i rum the macro give me open window then give me #VALUE! .
can you help me with this


As i said it's occurred due to mismatch in sheet name!

Your previous sheet name was "Date " where you have changed the same to "Data"
 
Dear ,
i appreciate your help i have other thing what i have to add to my code to make the data change automaticly whrn change the name without nedd to run the macro each time i change the name .

Thanks


Pls specify the range where name will be change.

 
Dear ,
attached the file i have problem with . is the problem the sheet name "data " how i can solve it if i have the same problem in other sheets .
other thing could you help me with i add total so i want the code take the cells before the total and after the total and not take the total cell ,

Thanks alot for your help


Revised code!

Code:
Sub test()
    With Sheet2
        .[c7:c11] = "=IF(B7<>""Total"",SUMIFS(Data!$D:$D,Data!$A:$A,$C$3,Data!$B:$B,$C$4,Data!$C:$C,B7),SUMIF($B$6:$B6,""<>""""Total"",$C$6:C6))"
        .[c7:c11].Value = .[c7:c11].Value
    End With
End Sub
 
Thanks alot for your help ,

* if i have two ranges c7:c10 then sum formula for the above then i have anothar range c12:c15 . so i want the code read the second range also .

* other thing each time i change the name "accounting , marketing .." i have to run the macro to change the figures is there an addition on code make the figures changed auto once i change the name without need to run the macro each time .

again thank for your help
 
Advance thanks
please help me with the code in the attached file using sumifs function, code is working but too long and repeated.
 

Attachments

  • Test Vb.xlsm
    21.8 KB · Views: 19
Hello vsubash

Just check "Deepak" and "Hui" which works fine.

June you need to repeat for the rest of the columns..and your Sheet name is "Data" not "Date".

Monty!
 
Back
Top