SUMIF using named range

[coop123]

Hi

I am trying to add a sumif formula to an existing macro. I am wanting to sum all minus values and all positive values. The start and end of the range being summed will change each time report is downloaded.

I have got to the top of my column of data as part of existing macro.

Dim Rng As Range
Set Rng = ActiveCell.End(xlDown)

Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Resize(4, 1).Select
Selection.Delete Shift:=xlUp

ActiveCell.Resize(1, 1).Select

ActiveCell.Offset(1, 1).Select

ActiveCell.FormulaR1C1 = "=SUMIF(Rng:R[-2]C2,"">0"",Rng:R[-2]C)"

Actual SUMIF formula is =SUMIF(B23:B40,"<0",B23:B40). Range B23:B40 will vary each time report is run.

Any help to correct my formula would very much be appreciated.

Thank you

Coop123

 

[Joe4]

I think it would be very helpful for us to see an example of your data structure, and you can show us an example of exactly what it is you are trying to do (so many of these solutions are really dependent on how the structure of the sheet is laid out).

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[coop123]

As requested I have pasted sample of my spreadsheet using XL2BB.

Thanks coop123

SAMPLE.xlsx
	A	B	C	D	E
1	Number	Stk Qty	Pro Rq Qty	Pro Rq Qty
2		Oct-23	Nov-23	Dec-23
3	17001URK1	-1918	661	518
4	17002URK2	-242	520	120
5	17003URK1	285	317	676
6	17201URK2	244	157	126
7	20001URK1	-4406	2102	1592
8	20201URK2	186	196	292
9	25001URK0	499	62	77
10	25201URK2	-6533	3454	3219
11	25202URK2	-501	267	161
12	25203URK2	363	212	240
13	25204URK2	0	0	0
14	25601URK2	447	40	40
15	25602URK1	-2	40	133
16	17004URK1	440	142	0
17	25204URK2	17	40	40
18	25603URK1	56	40	80
19
20		-13602	8250	7314
21		47737
22
23	3801URK2	-6094	2447	1710
24	3802URK2	48	0	0
25	3803URK0	-250	49	45
26	3801URK0	742	655	968
27	3802URK1	0	0	0
28	3802URK2	65	0	0
29	3803URK0	25	424	481
30	3801URK1	-224	520	120
31	3801URK1	-5111	2697	2431
32	3804URK2	-17	0	0
33	3805URK0	-231	136	104
34	3806URK0	-313	0	0
35	3801URK0	129	80	120
36	3802URK2	165	0	40
37	3803URK0	-509	855	881
38	4501URK0	-210	120	213
39	4502URK2	88	0	40
40	3801URK1	-484	267	161
41
42
43
Sheet1

Cell Formulas
Range	Formula
C20	C20	=SUM($C$3:$C18)
D20	D20	=SUM($D$3:$D18)
B20	B20	=SUMIF($B$2:$B18,"<0",$B$2:B18)
B21	B21	=SUMIF($B$2:$B19,">0",$B$2:B19)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:D	Cell Value	<0	text	YES

 

[Joe4]

So, you want to add the formulas after each section?
Are there always 4 rows in between each section?
If not, what does the data look like initially?

 

[coop123]

I have split the data by adding 4 rows, and added formula for top section. I need to add the formula at the bottom of the second section. There will only be 2 sections of data.

My example starts at row 23 but it could be 33 or 13. Similarly end row 40 in my example but could be 50 or 30. I need to set this range each time and use in SUMIF formula. at the bottom of last section of data.

Thanks

coop123

 

[Joe4]

So, you only want the formulas in between the two sections, in the blank rows you previously created?
Do you only want the formulas shown in column B, or the ones for columns C and D also?
Also, I assume that it was by mistake you include row 2 (the date) in your formulas? You really do not to include the date in your SUMs, do you ?

 

[Joe4]

If all my assumptions are correct, then try this:

Sub MyInsertFormulas()

    Dim lr As Long
    
'   Find last row in first block in column A
    lr = Range("A3").End(xlDown).Row
    
'   Insert formulas
    Range("B" & lr + 2).Formula = "=SUMIF(B3:B" & lr & ",""<0"",B3:B" & lr & ")"
    Range("B" & lr + 3).Formula = "=SUMIF(B3:B" & lr & ","">0"",B3:B" & lr & ")"
    Range("C" & lr + 2).Formula = "=SUM(C3:C" & lr & ")"
    Range("D" & lr + 2).Formula = "=SUM(D3:D" & lr & ")"
    
End Sub

 

[coop123]

I want the formula to be in row 42 in my example. Summing data from row 23 to 40 only. At the bottom of the last section.

The formulas in rows 20 & 21 I have already taken care of.

 

[Joe4]

I want the formula to be in row 42 in my example. Summing data from row 23 to 40 only. At the bottom of the last section.

The formulas in rows 20 & 21 I have already taken care of.

Very similar code:

Sub MyInsertBottomFormulas()

    Dim lr As Long
    Dim fr As Long
    
'   Find last row in last block of data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Find first row in last block of data in column A
    fr = Cells(lr, "A").End(xlUp).Row
    
'   Insert formulas
    Range("B" & lr + 2).Formula = "=SUMIF(B" & fr & ":B" & lr & ",""<0"",B" & fr & ":B" & lr & ")"
    Range("B" & lr + 3).Formula = "=SUMIF(B" & fr & ":B" & lr & ","">0"",B" & fr & ":B" & lr & ")"
    Range("C" & lr + 2).Formula = "=SUM(C" & fr & ":C" & lr & ")"
    Range("D" & lr + 2).Formula = "=SUM(D" & fr & ":D" & lr & ")"
    
End Sub

 

[coop123]

Thank you very much it had given me a problem trying to get the range into the formula. Your effort had been much appreciated.

Thanks again

coop123