Sumifs and Do While Loops - Help Needed

seanshurbet1

New Member
Joined
Mar 21, 2019
Messages
4
Hey -

So created t following code trying to mimic this formula ** =SUMIFS(OOR_Data!$L:$L,OOR_Data!$Q:$Q,"="&FF4_FF2!J$5,OOR_Data!$H:$H,FF4_FF2!$C9)

Code:
 i = 6


Do While Range("C" & i) <> ""

Set MyRg1 = Sheets("OOR_Data").Range("L:L")
Set MyRg2 = Sheets("OOR_Data").Range("Q:Q")
Set MyRg3 = Sheets("OOR_Data").Range("H:H")
Set MyRg4 = Sheets("FF4_FF2").Range("C" & i)
Set MyRg5 = Sheets("FF4_FF2").Range("I$5")

Range("I6:N" & i).Formula = Application.WorksheetFunction.SumIfs(MyRg1, MyRg2, " = " & MyRg5) & Application.WorksheetFunction.SumIfs(MyRg1, MyRg3, MyRg4)

i = i + 1

Loop[\code]

&

[code]'i = 6
'Do While Range("B" & i) <> ""
'Range("I6:N" & i) = Application.WorksheetFunction.SumIfs(Sheets("OOR_Data").Range("L:L"), Sheets("OOR_Data").Range("Q:Q"), " = " & Sheets("FF4_FF2").Range("I" & b), Sheets("OOR_Data").Range("H:H"), Sheets("FF4_FF2").Range("C" & i))
' SumProduct((Sheets("OOR_Data").Range("H3:H10000") = Sheets("FF4_FF2").Range("C" & i)) * (Sheets("OOR_Data").Range("R2:R10000") = Sheets("FF4_FF2").Range("I" & b)), (Sheets("OOR_Data").Range("L2:L10000")))

'i = i + 1


'Loop[\code]
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

seanshurbet1

New Member
Joined
Mar 21, 2019
Messages
4
Sorry - I messed up the code tags - but basically I am saying go to ws x and sum qty and next look at same page with dates is column q and = to ws y date "n row" and match the items in ws x to ws y

The formula works
 

Watch MrExcel Video

Forum statistics

Threads
1,114,636
Messages
5,549,103
Members
410,896
Latest member
sheymyster
Top