# Needs some help with a formula

Solved/Closed
dgcasey Posts 3 Registration date Tuesday November 25, 2014 Status Member Last seen November 25, 2014 - Nov 25, 2014 at 12:39 AM
dgcasey Posts 3 Registration date Tuesday November 25, 2014 Status Member Last seen November 25, 2014 - Nov 25, 2014 at 03:46 PM
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.

## 3 responses

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
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
dgcasey Posts 3 Registration date Tuesday November 25, 2014 Status Member Last seen November 25, 2014
Nov 25, 2014 at 03:43 PM
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. :)
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
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 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 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
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..