YEAR Formula

[Drewmyster]

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.

Please help.

 

[Weaver]

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

 

[Drewmyster]

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?

 

[Drewmyster]

Can anybody help with this?

 

[Richard Schollar]

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.