Related:

- Needs some help with a formula
- Excel formula bar missing - How-To - Excel
- Spreadsheet formula - Articles
- How to copy data from one sheet to another in excel using formula - How-To - Excel
- 1st, 2nd, 3rd position formula in excel ✓ - Forum - Office Software
- Excel @ in formula ✓ - Forum - Excel

Mazzaropi

- Posts
- 1868
- Registration date
- Monday August 16, 2010
- Status
- Contributor
- Last seen
- January 15, 2021

Is this what you want?

I hope it helps.

--

Belo Horizonte, Brasil.

Marcílio Lobão

vcoolio

- Posts
- 1270
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 20, 2021

Hello dgcasey,

Perhaps a helper column could solve this for you.

From what I understand, you would like a formula to total each year in groups. For example, 2011 has two entries totalling $139.95, 2012 has four entries totalling $149.79 and so on regardless of where the years appear in your dataset. If this is correct then try this:-

Column A has your dates.

Column B has the $ amounts.

So far so good.

In cell C3, enter =Year(A3) as your dates start in cell A3. Drag this down as far as you like. The years only should appear in Column C.

In column E, type in the years (one year per row): 2008 in E2, 2011 in E3, 2012 in E4 etc..

In cell F3, enter the following formula:-

=SUMIF($C$3:$C$12, E2, $B$3:$B$12) and drag this down as far as you like.

You can expand the ranges as required.

The totals for each year group should now appear in Column F.

I left Column D blank just to avoid clutter.

I've attached an example for you to have a look at. In the example, you will see that I started in Row 2.

https://www.dropbox.com/s/3ys5b45mcjwdo2d/Mr%20Casey%27s%20Dilemma.xlsx?dl=0

I hope this helps reduce the swelling in your bum!!

Cheers,

vcoolio

Perhaps a helper column could solve this for you.

From what I understand, you would like a formula to total each year in groups. For example, 2011 has two entries totalling $139.95, 2012 has four entries totalling $149.79 and so on regardless of where the years appear in your dataset. If this is correct then try this:-

Column A has your dates.

Column B has the $ amounts.

So far so good.

In cell C3, enter =Year(A3) as your dates start in cell A3. Drag this down as far as you like. The years only should appear in Column C.

In column E, type in the years (one year per row): 2008 in E2, 2011 in E3, 2012 in E4 etc..

In cell F3, enter the following formula:-

=SUMIF($C$3:$C$12, E2, $B$3:$B$12) and drag this down as far as you like.

You can expand the ranges as required.

The totals for each year group should now appear in Column F.

I left Column D blank just to avoid clutter.

I've attached an example for you to have a look at. In the example, you will see that I started in Row 2.

https://www.dropbox.com/s/3ys5b45mcjwdo2d/Mr%20Casey%27s%20Dilemma.xlsx?dl=0

I hope this helps reduce the swelling in your bum!!

Cheers,

vcoolio

vcoolio

- Posts
- 1270
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 20, 2021

Sorry dgcasey,

As you are starting in row 3.

this line:-

In column E, type in the years (one year per row): 2008 in E2, 2011 in E3, 2012 in E4 etc..

should read:-

In column E, type in the years (one year per row): 2008 in E3, 2011 in E4, 2012 in E5 etc..

and in the formula, E2 should read E3.

Regards,

vcoolio.

As you are starting in row 3.

this line:-

In column E, type in the years (one year per row): 2008 in E2, 2011 in E3, 2012 in E4 etc..

should read:-

In column E, type in the years (one year per row): 2008 in E3, 2011 in E4, 2012 in E5 etc..

and in the formula, E2 should read E3.

Regards,

vcoolio.

dgcasey

- Posts
- 3
- Registration date
- Tuesday November 25, 2014
- Status
- Member
- Last seen
- November 25, 2014

Thanks for your help on this. I actually went with Mazz's solution above and it so elegantly simple and that is probably why I couldn't see it. I was looking for something very complex and it didn't need to be.

=SUMPRODUCT((YEAR('Art sold'!$A$3:$A$2000)=$D4)*('Art shipped'!G$3:G$2000))

I'd never seen this formula written this way. You saved me a few more hours of pulling my hair out. :)