# YEAR Formula

#### Drewmyster

##### Board Regular
I need to use the YEAR formula to work out the sum of transactions from a specified year.

I've tried formatting the cell to Text as well as custom "YYYY" but it still seems to do nothing.

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Are you sure this is what you need to do?

I'm guessing you have a series of transactions where the date is in one column and the transaction amounts are in another.

You can create a pivot table and group dates as years in the row header and in the data area, use the sum of your transaction. This will collate the total of each transaction into the relevant year.

There are other ways, if this doesn't suit.

Say dates in B2:B200, amounts in C2:C200

=sumproduct(--(year(B2:B200)=2010),C2:C200)

HTH

Perhaps this incomplete formula will help explain what I'm trying to achieve;

=IF(\$A7="","",SUMPRODUCT(--(YEAR(CAPITAL!\$A\$5:\$A\$1000)=\$A7),INDEX(CAPITAL!\$B\$5:\$H\$1000,0,MATCH(C\$4,CAPITAL!\$B\$4:\$H\$4,0))))

As you can see, I not only need to look up a year, but also look up specific transactions which match the text string in C4, as the same formula would be used for D4, E4, F4 etc.

Any thoughts?

Can anybody help with this?

It's a bit difficultto know what might be going wrong without seeing any sample data (including the header rows) - can you use one of the html makers to post some sample data (doesn't need to be a lot) and include an explanation of what the output should be given the sample? You can use the link in my signature to source one such html maker.

Replies
3
Views
101
Replies
2
Views
208
Replies
6
Views
196
Replies
7
Views
501
Replies
6
Views
331

1,196,430
Messages
6,015,222
Members
441,882
Latest member
rcgyuk

### 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?

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