VBA Sumifs Criteria based on cell values

[T_Khan]

Hi,

I have two sheets:

1. Following Sheet where the Data is

2. Following Sheet where the data should come through SUMIFS VBA

Value should be summed based on following criteria;

1. Criteria 1: Account
2. Criteria 2: Costcenter

 

[maabadi]

Why you have repeated account & Costcenter at Second Sheet?

If you don't want repeated, you can do it very easily with Pivot tables?

 

[T_Khan]

Thank you maabadi for your reply. Actually it is one of the part of the process that I need to automate. I have done other parts but I am not able to to do this part.

In other words I have to use "Sumifs " in VBA for the above mentioned problem.

 

[maabadi]

You don't answer my question. For example, you have two 1248 & two 3214.
You want one from each or you want two for each one.

 

[T_Khan]

In Column there are Cost Centers and in Rows there are account numbers.

I want Sumifs only on those cells where Row is account no and Column is cost center. I dont want to use Sumifs where Row is having account desription (Personal Cost, Electricity Cost, Water Cost) and Column is having Division description (Division 1, Division 2, Division 3) otherweise already existing sum formula in these cells will be deleted.

 

[maabadi]

I didn't see at first file Divisions & Costs. Please upload example file & Desired result with XL2BB ADDIN (Preferable) (you can see it at top menu of reply section) or upload at free hosting site e.g. www.dropbox.com, GoogleDrive , ... & Insert link here.

 

[maabadi]

Without having your Data & if your data is in Sheet1 try this macro & see Result at Sheet2:

Sub UNIQUE4()
Dim a As Variant, i As Long, o As Variant, d As Object, b As Variant, f As Object, Lr As Long, Lc As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")
  a = .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
  b = .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row)
  Set d = CreateObject("Scripting.Dictionary")
  Set f = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(a, 1)
      d(a(i, 1)) = d(a(i, 1))
      f(b(i, 1)) = f(b(i, 1))
    Next i
  Sheets("Sheet2").Range("A2").Resize(d.Count) = Application.Transpose(Array(d.Keys))
  Sheets("Sheet2").Range("B1").Resize(, f.Count) = f.Keys
End With
With Sheets("Sheet2")
Lr = .Range("A" & Rows.Count).End(xlUp).Row
Lc = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range("B2").FormulaR1C1 = "=SUMIFS(Sheet1!R2C3:R" & Lr & "C3,Sheet1!R2C1:R" & Lr & "C1,Sheet2!RC1,Sheet1!R2C2:R" & Lr & "C2,Sheet2!R1C)"
.Range("B2").AutoFill Destination:=.Range("B2:B" & Lr), Type:=xlFillDefault
.Range("B2:B" & Lr).AutoFill Destination:=.Range(.Cells(2, 2), .Cells(Lr, Lc)), Type:=xlFillDefault
Range(.Cells(2, 2), .Cells(Lr, Lc)).Value = Range(.Cells(2, 2), .Cells(Lr, Lc)).Value
End With

Application.ScreenUpdating = True
End Sub

 

[T_Khan]

Thank you for your reply maabadi. The above written code does not work and I want to send you my file with desired results through google drive. However, I need you email adress in order to share this file with you.

I have tried the other option "XL2BB ADDIN" but it does not work.

 

[Fluff]

You need to mark the file for sharing & then post the link you get to the site.
If files are shared they need to be accessible to all members not just some.