SUMIFS

[Haree]

Hello all my data is from column A to column H
I am using the below mentioned formula
=SUMIFS($C$2:$C$2002,$G$2:$G$2002,12,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF((G:G<=3), $R$1+1, $R$1))
The if part IF((G:G<=3), $R$1+1, $R$1)) isn't working correctly
My actual requirement is column g has numbers from 1 to 12 and column H has years . I have a year referenced in R1 if column g's value is below or equal to 3 the corresponding H value should be R1+1 else it should be R1 this is the criteria

Any help would be greatly appreciated.
Thanks

 

[awoohaw]

What is in $R$1? Providing a sample of data would be extremely helpful. If possible please use the xl2bb add in (link below) to post a mini workbook. If you cannot use that then please post a column and row labeled table. What other values can be in Column B? What values and data types are in column H?

Please help the forum help you. Thanks in advance.

 

[Haree]

Hello @awoohaw Thank you for your time and Patience . Attaching a sample of rough data with XL2bb

Gold Stock New.xlsm
	A	B	C	D	E	F	G	H
1	Date	Particulars	No of Pieces	Weight	Cl.Bal (P)	Cl.Bal (W)	Month	Year
2	######	Opening Balance	436	1267.5	436	1267.5	12	2023
3	######	Sales	1	4.15	435	1263.35	12	2023
4	######	Sales	1	4.21	434	1259.14	12	2023
5	######	Sales	1	1.54	433	1257.6	12	2023
6	######	Sales	1	2.1	432	1255.5	12	2023
7	######	Sales	1	4.38	431	1251.12	1	2024
8	######	Sales	1	2.09	430	1249.03	1	2024
9	######	Sales	1	2.03	429	1247	1	2024
10	######	Sales	1	2.26	428	1244.74	1	2024
11	######	Sales	1	2.99	427	1241.75	1	2024
12	######	Sales	1	1.17	426	1240.58	1	2024
13	######	Sales	1	4.15	425	1236.43	1	2024
14	######	Purchase	1	4.14	426	1240.57	7	2024
15	######	Purchase	10	36.31	436	1276.88	10	2024
Kammal (Pair)

Cell Formulas
Range	Formula
E3:E15	E3	=IF(ISBLANK(A3),"",IF(B3="Sales",E2-C3,E2+C3))
F3:F15	F3	=IF(ISBLANK(A3),"",IF(B3="Sales",F2-D3,F2+D3))
G3:G15	G3	=IF(ISBLANK(A3),"",MONTH(A3))
H3:H15	H3	=IF(ISBLANK(A3),"",YEAR(A3))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B15	Cell Value	contains "Purchase"	text	NO
B15	Cell Value	contains "Sales"	text	NO
B14	Cell Value	contains "Purchase"	text	NO
B14	Cell Value	contains "Sales"	text	NO
B13	Cell Value	contains "Purchase"	text	NO
B13	Cell Value	contains "Sales"	text	NO
B12	Cell Value	contains "Purchase"	text	NO
B12	Cell Value	contains "Sales"	text	NO
B11	Cell Value	contains "Purchase"	text	NO
B11	Cell Value	contains "Sales"	text	NO
B10	Cell Value	contains "Purchase"	text	NO
B10	Cell Value	contains "Sales"	text	NO
B9	Cell Value	contains "Purchase"	text	NO
B9	Cell Value	contains "Sales"	text	NO
B8	Cell Value	contains "Purchase"	text	NO
B8	Cell Value	contains "Sales"	text	NO
B7	Cell Value	contains "Purchase"	text	NO
B7	Cell Value	contains "Sales"	text	NO
B6	Cell Value	contains "Purchase"	text	NO
B6	Cell Value	contains "Sales"	text	NO
B5	Cell Value	contains "Purchase"	text	NO
B5	Cell Value	contains "Sales"	text	NO
B4	Cell Value	contains "Purchase"	text	NO
B4	Cell Value	contains "Sales"	text	NO
B3	Cell Value	contains "Purchase"	text	NO
B3	Cell Value	contains "Sales"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B3:B15	List	Purchase,Sales

Gold Stock New.xlsm
	O	P	Q	R	S	T	U	V
1			Year Starting	2023	No Match	No Match
2
3	Month	Purchase P	Purchase W	Sales P	Sales W	Closing Bal P	Closing Bal W
4	April	0	0	0	0	436	1267.5
5	May	0	0	0	0	436	1267.5
6	June	0	0	0	0	436	1267.5
7	July	1	0	0	0	437	1267.5
8	August	0	0	0	0	437	1267.5
9	September	0	0	0	0	437	1267.5
10	October	10	0	0	0	447	1267.5
11	November	0	0	0	0	447	1267.5
12	December	0	0	4	12	443	1255.5
13	January	0	0	0	0	443	1255.5
14	February	0	0	0	0	443	1255.5
15	March	0	0	0	0	443	1255.5
Kammal (Pair)

Cell Formulas
Range	Formula
P4	P4	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,4,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND($G:$G>=1, $G:$G<=3), $R$1+1, $R$1))
Q4	Q4	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,4,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND($G:$G>=1, $G:$G<=3), $R$1+1, $R$1))
R4	R4	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,4,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND($G:$G>=1, $G:$G<=3), $R$1+1, $R$1))
S4	S4	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,4,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND($G:$G>=1, $G:$G<=3), $R$1+1, $R$1))
T4	T4	=IF(YEAR(A2)=R1,E2+P4-R4,S1+P4-R4)
U4	U4	=IF(YEAR(A2)=R1,F2+Q4-S4,T1+Q4-S4)
P5	P5	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,5,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(G:G>=1, G:G<=3), $R$1+1, $R$1))
Q5	Q5	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,5,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(H:H>=1, H:H<=3), $R$1+1, $R$1))
R5	R5	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,5,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(I:I>=1, I:I<=3), $R$1+1, $R$1))
S5	S5	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,5,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(J:J>=1, J:J<=3), $R$1+1, $R$1))
T5:U15	T5	=T4+P5-R5
P6	P6	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,6,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(G:G>=1, G:G<=3), $R$1+1, $R$1))
Q6	Q6	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,6,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(H:H>=1, H:H<=3), $R$1+1, $R$1))
R6	R6	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,6,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(I:I>=1, I:I<=3), $R$1+1, $R$1))
S6	S6	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,6,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(J:J>=1, J:J<=3), $R$1+1, $R$1))
P7	P7	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,7,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(G:G>=1, G:G<=3), $R$1+1, $R$1))
Q7	Q7	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,7,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(H:H>=1, H:H<=3), $R$1+1, $R$1))
R7	R7	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,7,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(I:I>=1, I:I<=3), $R$1+1, $R$1))
S7	S7	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,7,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(J:J>=1, J:J<=3), $R$1+1, $R$1))
P8	P8	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,8,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(G:G>=1, G:G<=3), $R$1+1, $R$1))
Q8	Q8	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,8,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(H:H>=1, H:H<=3), $R$1+1, $R$1))
R8	R8	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,8,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(I:I>=1, I:I<=3), $R$1+1, $R$1))
S8	S8	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,8,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(J:J>=1, J:J<=3), $R$1+1, $R$1))
P9	P9	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,9,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(G:G>=1, G:G<=3), $R$1+1, $R$1))
Q9	Q9	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,9,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(H:H>=1, H:H<=3), $R$1+1, $R$1))
R9	R9	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,9,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(I:I>=1, I:I<=3), $R$1+1, $R$1))
S9	S9	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,9,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(J:J>=1, J:J<=3), $R$1+1, $R$1))
P10	P10	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,10,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(G:G>=1, G:G<=3), $R$1+1, $R$1))
Q10	Q10	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,10,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(H:H>=1, H:H<=3), $R$1+1, $R$1))
R10	R10	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,10,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(I:I>=1, I:I<=3), $R$1+1, $R$1))
S10	S10	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,10,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(J:J>=1, J:J<=3), $R$1+1, $R$1))
P11	P11	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,11,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(G:G>=1, G:G<=3), $R$1+1, $R$1))
Q11	Q11	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,11,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(H:H>=1, H:H<=3), $R$1+1, $R$1))
R11	R11	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,11,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(I:I>=1, I:I<=3), $R$1+1, $R$1))
S11	S11	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,11,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(J:J>=1, J:J<=3), $R$1+1, $R$1))
P12	P12	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,12,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(G:G>=1, G:G<=3), $R$1+1, $R$1))
Q12	Q12	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,12,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(H:H>=1, H:H<=3), $R$1+1, $R$1))
R12	R12	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,12,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(I:I>=1, I:I<=3), $R$1+1, $R$1))
S12	S12	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,12,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(J:J>=1, J:J<=3), $R$1+1, $R$1))
P13	P13	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,1,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(G:G>=1, G:G<=3), $R$1+1, $R$1))
Q13	Q13	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,1,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(H:H>=1, H:H<=3), $R$1+1, $R$1))
R13	R13	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,1,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(I:I>=1, I:I<=3), $R$1+1, $R$1))
S13	S13	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,1,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(J:J>=1, J:J<=3), $R$1+1, $R$1))
P14	P14	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,2,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(G:G>=1, G:G<=3), $R$1+1, $R$1))
Q14	Q14	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,2,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(H:H>=1, H:H<=3), $R$1+1, $R$1))
R14	R14	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,2,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(I:I>=1, I:I<=3), $R$1+1, $R$1))
S14	S14	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,2,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(J:J>=1, J:J<=3), $R$1+1, $R$1))
P15	P15	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,3,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(G:G>=1, G:G<=3), $R$1+1, $R$1))
Q15	Q15	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,3,$B$2:$B$2002,"Purchase",$H$2:$H$2002,IF(AND(H:H>=1, H:H<=3), $R$1+1, $R$1))
R15	R15	=SUMIFS($C$2:$C$2002,$G$2:$G$2002,3,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(I:I>=1, I:I<=3), $R$1+1, $R$1))
S15	S15	=SUMIFS($D$2:$D$2002,$G$2:$G$2002,3,$B$2:$B$2002,"Sales",$H$2:$H$2002,IF(AND(J:J>=1, J:J<=3), $R$1+1, $R$1))

Cells with Data Validation
Cell	Allow	Criteria
R1	List	2023,2024,2025,2026,2027

Regards
Haree Nagappan