SUM column variable criteria

[dj_danu01]

Hi everyone,

I know this title seems too easy for some of you, but i couldn't made it! i kindly eed your help to solve it, plz!

I'll attach my excel file to this post, here is my pb:
Column A (Week) and Column B (Product) both criteria i need to select to Sum up only visible value from the column C (at the end of the column), and on the same method do it for column G (NOK).

I tried SUMIFS(...), Index(... Match(...)), VLOOKUP().
it doesn't work, i don't know why. But the columns A to H generate from another excel file, so lenth of the colmn can be 600 or more than 10000.

SO i want to do in macro here what i have done:

Please Login or Register  to view this content.

xl.jpgBook2.xls

if someone make this macro more light n appicable, i'll be very glad!

Thank you.

[Solus Rankin]

celv and celv1 aren't named ranges. If the Range you are trying to select is the info held in those string variables then remove the "".

[dj_danu01]

@ XeRo Solus, Thanks , i tried Range(celv) without "" , it's still not working!
What should i named the variables "celv" and "celv1"?

in the file i attached, the macro is inside, so in this post, the progrm is for a overall view!
But i nw i added the [CODE], [\CODE]! guess it should ok, now!

[Solus Rankin]

Well those names are fine. There are a couple undefined variables and some selection where there doesn't have to be, so first lets try to establish exactly what you're trying to achieve.

According to you thread title you'd like to sum based on a criteria, are you copy and pasting to remove formulas?

[Alliswellandwell]

Hello,

Please see attached image and do the needful.

I need SUMIFS formula for multiple criteria in a particular selected range.

Thank you all in advance.SUMIFS formula for multiple criterias in slected range...jpg

[FDibbins]

Alliswell, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[jolivanes]

Alliswellandwell
Please read the forum rules.
In your case, pay attention to #2.

http://www.excelforum.com/forum-rule...rum-rules.html

OK, FDibbins was faster.

[FDibbins]

DJ, I dont quite understand what you are trying to sum on the table on the right? I can do a sum based on the week, and can probably do a sum based on...
Sum N.iO
Sum checked
PPM
IF i knew where they were lol

[dj_danu01]

hello guys, sorry for the late reply.
This code is working:

[CODE]
Sub SumUp()
Dim celv As Range
Dim celv1 As Range
Dim i As Integer
Dim k As Integer

With Sheets("Sheet1")
.AutoFilterMode = False
On Error Resume Next

'For Each celv In Range("L4:V10")

'For Each celv1 In Range("L5:V11")

For i = 12 To 22 Step 1
For k = 4 To 10 Step 3
Set celv = Cells(k, i).Value
Set celv1 = Cells(k + 1, i).Value
With Sheets("Sheet1").Range("A1:H1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:=Worksheets("Sheet1").Cells(3, i + 1).Value
.AutoFilter Field:=2, Criteria1:=Worksheets("Sheet1").Cells(k, "J").Value

End With

Dim Lr As Long

Lr = .Range("G" & Rows.Count).End(xlUp).Row
Range("L14").Value = WorksheetFunction.Subtotal(9, .Range("G2:G" & Lr))

Range("L14").Select
Selection.Copy
celv.Select
ActiveSheet.Paste
Cells(k, i + 1).Value = celv


Lr = .Range("C" & Rows.Count).End(xlUp).Row
Range("L15").Value = WorksheetFunction.Subtotal(9, .Range("C2:C" & Lr))
Range("L15").Select
Selection.Copy
celv1.Select
ActiveSheet.Paste
Cells(k + 1, i + 1).Value = celv1

.Range("L14").Clear
.Range("L4").Clear
.Range("L15").Clear
.Range("L5").Clear
.AutoFilterMode = False

Next k
Next i
'Next celv1
'Next celv
On Error GoTo 0

End With
End Sub

[\CODE]


Thank you!