Needs some help with a formulaSolved/Closed

Question

Hello, Configuration: Windows XP / Firefox 33.0 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.

vcoolio · Answer

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

vcoolio · Answer

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.

Mazzaropi · Answer

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

Needs some help with a formula

3 replies

Subscribe To Our Newsletter!