Less Hacky Sumifs?

[evtom76]

Hi all,

Long-time listener, first time caller! I got some good help trying to get this SumIfs working from previous examples on here - I really just need to add up all positive share numbers each year.

My example below is trying to add up all the positive numbers in calendar year 2023 - to be a running total to indicate a problem if shares granted in a calendar year exceed an annual cap.

So far, I had to use a helper YEAR( ) column - is there a more intelligent way to do this without having to add a helper column to a few dozen tabs?

Here's the formula I'm trying to upgrade by taking out the YEAR helper column:

=SUMIFS(D3:D12,B3:B12,"2023",D3:D12,">=0")

Thanks for any help!

Evan

Example for SumIf Q.xlsx
	A	B	C	D	E
1	Holder 1
2	Date	[Year Helper]	Transaction	Change to Holdings
3	3/1/2022	2022	net for taxes	(562)
4	3/2/2022	2022	net for taxes	(165)
5	5/4/2022	2022	net for taxes	(165)
6	12/31/2022	2022	401(k) payroll contributions for 2022	0
7	2/1/2023	2023	Performance shares vesting	1,354
8	2/1/2023	2023	Sale	(6,151)
9	2/1/2023	2023	Award of performance shares	6,151
10	3/1/2023	2023	Award	21,351
11	3/1/2023	2023	Tax Redemption	(615)
12	3/2/2023	2023	Tax Redemption	(615)
13				Shares Awarded 2023	28,856.00
Sheet1

Cell Formulas
Range	Formula
B3:B12	B3	=YEAR(A3)
E13	E13	=SUMIFS(D3:D12,B3:B12,"2023",D3:D12,">=0")

 

[Joe4]

Welcome to the Board!

Try this:

=SUMPRODUCT(--(YEAR(A3:A12)=2023),--(D3:D12>0),D3:D12)

Note there is no need for the equal sign in ">=0". Adding zero to a sum does nothing. So you can just use ">0".

 

[awoohaw]

try this:

mr excel questions 13.xlsm
	A	B	C	D	E
1	Holder 1
2	Date	[Year Helper]	Transaction	Change to Holdings
3	2022-03-01	2022	net for taxes	-562
4	2022-03-02	2022	net for taxes	-165
5	2022-05-04	2022	net for taxes	-165
6	2022-12-31	2022	401(k) payroll contributions for 2022	0
7	2023-02-01	2023	Performance shares vesting	1354
8	2023-02-01	2023	Sale	-6151
9	2023-02-01	2023	Award of performance shares	6151
10	2023-03-01	2023	Award	21351
11	2023-03-01	2023	Tax Redemption	-615
12	2023-03-02	2023	Tax Redemption	-615	28856
13	#VALUE!			Shares Awarded 2023	28856
Sheet3

Cell Formulas
Range	Formula
B3:B12	B3	=YEAR(A3)
A13	A13	=DATE( MID(A3,FIND("/",A3,FIND("/",A3,1)+1)+1,4), LEFT(A3,FIND("/",A3,1)-1), MID(A3,FIND("/",A3,1)+1,FIND("/",A3,FIND("/",A3,1))-1) )
E12	E12	=SUMPRODUCT((--($D$3:$D$12>0))*($D$3:$D$12)*(--(YEAR($A$3:$A$12)=2023)))
E13	E13	=SUMIFS(D3:D12,B3:B12,"2023",D3:D12,">=0")

 

[MARK858]

Another option

Book1
	A	B	C	D
1	Holder 1
2	Date	Transaction	Change to Holdings
3	01/03/2022	net for taxes	-562
4	02/03/2022	net for taxes	-165
5	04/05/2022	net for taxes	-165
6	31/12/2022	401(k) payroll contributions for 2022	0
7	01/02/2023	Performance shares vesting	1354
8	01/02/2023	Sale	-6151
9	01/02/2023	Award of performance shares	6151
10	01/03/2023	Award	21351
11	01/03/2023	Tax Redemption	-615
12	02/03/2023	Tax Redemption	-615
13			Shares Awarded 2023	28856
Sheet3

Cell Formulas
Range	Formula
D13	D13	=SUMIFS($C$3:$C$12,$C$3:$C$12,">0",$A$3:$A$12,">="&DATE(2023,1,1))

Please note that my dates are real dates in dd/mm/yyyy format