Less Hacky Sumifs?

evtom76

New Member
Joined
Mar 7, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1Holder 1
2Date[Year Helper]TransactionChange to Holdings
33/1/20222022 net for taxes (562)
43/2/20222022 net for taxes (165)
55/4/20222022 net for taxes (165)
612/31/20222022 401(k) payroll contributions for 2022 0
72/1/20232023 Performance shares vesting 1,354
82/1/20232023 Sale (6,151)
92/1/20232023 Award of performance shares 6,151
103/1/20232023 Award 21,351
113/1/20232023 Tax Redemption (615)
123/2/20232023 Tax Redemption (615)
13Shares Awarded 202328,856.00
Sheet1
Cell Formulas
RangeFormula
B3:B12B3=YEAR(A3)
E13E13=SUMIFS(D3:D12,B3:B12,"2023",D3:D12,">=0")
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Welcome to the Board!

Try this:
Excel Formula:
=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".
 
Upvote 0
try this:
mr excel questions 13.xlsm
ABCDE
1Holder 1
2Date[Year Helper]TransactionChange to Holdings
32022-03-012022 net for taxes -562
42022-03-022022 net for taxes -165
52022-05-042022 net for taxes -165
62022-12-312022 401(k) payroll contributions for 2022 0
72023-02-012023 Performance shares vesting 1354
82023-02-012023 Sale -6151
92023-02-012023 Award of performance shares 6151
102023-03-012023 Award 21351
112023-03-012023 Tax Redemption -615
122023-03-022023 Tax Redemption -61528856
13#VALUE!Shares Awarded 202328856
Sheet3
Cell Formulas
RangeFormula
B3:B12B3=YEAR(A3)
A13A13=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) )
E12E12=SUMPRODUCT((--($D$3:$D$12>0))*($D$3:$D$12)*(--(YEAR($A$3:$A$12)=2023)))
E13E13=SUMIFS(D3:D12,B3:B12,"2023",D3:D12,">=0")
 
Upvote 0
Another option
Book1
ABCD
1Holder 1
2DateTransactionChange to Holdings
301/03/2022 net for taxes -562
402/03/2022 net for taxes -165
504/05/2022 net for taxes -165
631/12/2022 401(k) payroll contributions for 2022 0
701/02/2023 Performance shares vesting 1354
801/02/2023 Sale -6151
901/02/2023 Award of performance shares 6151
1001/03/2023 Award 21351
1101/03/2023 Tax Redemption -615
1202/03/2023 Tax Redemption -615
13Shares Awarded 202328856
Sheet3
Cell Formulas
RangeFormula
D13D13=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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top