Needs some help with a formula
Solved/Closed
dgcasey
dgcasey
- Posts
- 3
- Registration date
- Tuesday November 25, 2014
- Status
- Member
- Last seen
- November 25, 2014
dgcasey
- Posts
- 3
- Registration date
- Tuesday November 25, 2014
- Status
- Member
- Last seen
- November 25, 2014
Related:
- Needs some help with a formula
- Excel @ in formula ✓ - Forum - Excel
- 1st, 2nd, 3rd position formula in excel ✓ - Forum - Office Software
- Little alchemy formulas - Guide
- Logitech formula vibration feedback wheel driver windows 10 - Download
- Ms excel marksheet grade formula - Guide
3 replies
Mazzaropi
Nov 25, 2014 at 09:24 AM
- Posts
- 1963
- Registration date
- Monday August 16, 2010
- Status
- Contributor
- Last seen
- April 25, 2022
Nov 25, 2014 at 09:24 AM
dgcasey, Good morning.
Try to do:
=SUMPRODUCT((YEAR(A3:A12)=2008)*(B3:B12))
Is this what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Try to do:
=SUMPRODUCT((YEAR(A3:A12)=2008)*(B3:B12))
Is this what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
vcoolio
Nov 25, 2014 at 05:08 AM
- Posts
- 1356
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- August 11, 2022
Nov 25, 2014 at 05:08 AM
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
Nov 25, 2014 at 07:21 AM
- Posts
- 1356
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- August 11, 2022
Nov 25, 2014 at 07:21 AM
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
Nov 25, 2014 at 03:46 PM
- Posts
- 3
- Registration date
- Tuesday November 25, 2014
- Status
- Member
- Last seen
- November 25, 2014
Nov 25, 2014 at 03:46 PM
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.
Nov 25, 2014 at 03:43 PM
=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. :)