Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Needs some help with a formula [Solved/Closed]

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



Okay, here is a problem I've been dealing with for over an hour now. This should be easy, but it is kicking my butt. I have a spreadsheet with two columns, A and B. Column A has dates and Column B has dollar amounts. I want to be able to write a formula that shows me a total amount of dollars for a date range, i.e. show me the total for 2008. I wrote the following formula:

=SUM(IF((A3:A12>="01/01/2008") + (A3:A12<="12/31/2008"),(B3:B12)))

It doesn't work. I get the entire total for Col A. I have played with putting the <= and >= inside and outside the quotes. Nothing doing. I have removed parentheses and brought the conditions into the same set of parentheses. Not a chance.

The formula above does do some bit of the work, as I said, it will show me the total for the entire column. The funny thing is, it doesn't matter what dates I put in the formula, I still get the same total. I could change the dates to "apples" and "oranges" and I still get the same total.

And it doesn't matter if I press CTRL-SHIFT-ENTER. That don't do it either.

I have another formula in the same spreadsheet that counts how many dates:

=COUNTIF(A3:A12,">=1/1/2011")-COUNTIF(A3:A12,">12/31/2011")

No amount of modifying that formula will work for the first one.

Please help! Thanks.

See more

3 replies

Answer 1 / 3
Posts
1868
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
January 15, 2021
 139
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
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report
Posts
3
Registration date
Tuesday November 25, 2014
Status
Member
Last seen
November 25, 2014
Thanks Mazz, This worked perfectly after modification to pull amounts from two different spreadsheets in the workbook.

=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. :)
Answer 2 / 3
Posts
1270
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 20, 2021
 214
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
Answer 3 / 3
Posts
1270
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 20, 2021
 214
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.
Report
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.
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!