SUMIFS based on variable entered through InputBox

[richardturnbull]

Hi, I wonder if someone may be able to help me get out of a hole I have dug for myself

Here is some sample data:
Capture.PNG

I need to do two different kinds of calculations on a different sheet. The first is just summing the amount per school on a specific week (variable LastWeek entered by a user through an InputBox at the beginning of the routine):

Range("I29,I30,I31,I32,I33,I34,I35,I37,I38,I39,I40,I41").FormulaR1C1 = "=SUMIFS('ALL DATA'!C23,'ALL DATA'!C1,Report!RC[-8],'ALL DATA'!C25,""13-14 Wk " & LastWeek & """)"

(where the first criteria C23 is the amount, the second criteria C1 is the school name and the third criteria C25 is the week number)

That works fine.

But the second, I need to sum all weeks up to the current week (year to date), based on the week that the user enters (it is not necessarily THIS week). So in the image above, if the user entered that it is week 3, I would like to sum the amount for weeks 1, 2 and 3, for each individual school. Is the user entered 7, then it would sum all weeks up to week 7.

I really don't know where to start. Should I create some kind of variable array on a different sheet that is built after the user enters the week number, and then try to reference the data against that? I think I have reached my limits in VBA!

Sorry if my explanation is not great, it's hard to explain, but any help or pointers would be most gratefully appreciated!

Many thanks in advance for any help you may be able to offer.
Richard

[LeoTaxi]

codes on 2 buttons in file

Sub till_week()
wknr = InputBox("Till Weeknumber ?")
If wknr = "" Then Exit Sub
lr = Range("B" & Rows.Count).End(xlUp).Row
Range("B2", "B" & lr).Copy Range("F2")
Range("F2:F" & lr).RemoveDuplicates Columns:=1, Header:=xlNo
lr2 = Range("F" & Rows.Count).End(xlUp).Row
Range("F1").Value = "Total Till Week " & wknr
For i = 2 To lr2
    For x = 2 To lr
        If Val(Right(Range("A" & x), 2)) <= wknr And Range("B" & x) = Range("F" & i) Then
            tot = tot + Range("C" & x).Value
        End If
    Next x
    Range("G" & i) = tot
Next i
End Sub
Sub week()
wknr = InputBox("Weeknumber ?")
If wknr = "" Then Exit Sub
lr = Range("B" & Rows.Count).End(xlUp).Row
Range("B2", "B" & lr).Copy Range("I2")
Range("I2:I" & lr).RemoveDuplicates Columns:=1, Header:=xlNo
lr2 = Range("I" & Rows.Count).End(xlUp).Row
Range("I1").Value = "Total From Week " & wknr
For i = 2 To lr2
    For x = 2 To lr
        If Val(Right(Range("A" & x), 2)) = wknr And Range("B" & x) = Range("I" & i) Then
            tot = tot + Range("C" & x).Value
        End If
    Next x
    Range("J" & i) = tot
Next i
End Sub

Kind regards
Leo

[AlphaFrog]

Alternatively, you could extract the weeknumber values in a separate data column and use the SUMIFS criteria on the numeric values in that column.

Put this in D2 and copy down to return the numeric week numbers.
=RIGHT(A2,2)*1

Then use something like this as the column D sumif criteria where intLastWeek is an integer
""<=" & intLastWeek & """

[richardturnbull]

Thank you Antwerp and Florida! Thanks so much to both of you... both solutions work well, but AlphaFrog, yours fits the bill nicely for my needs (now why didn't I think of that . Easy when you know how.

This forum is excellent, thanks so much for your time and expertise. Thank you from Chichester, UK!