Sum IF or Vlookup

[SethP]

I wansn't sure what to name this thread as I am not sure how to solve the problem. Here is what my spreadsheet looks like:


Column: C E
1-2-00 $16.50
2-4-01 $11.25
6-10-02 $10.00
etc.
I have several rows of data and I need the formula add the dollar amount in column E for a specific year.
EX- if I add all of year 2000 then the result would be $16.50, and so on.

Any ideas??
Your help would be greatly apprieciated!
Seth

 

[dawnd3]

You would probably want to do a pivot table. In the pivot table have the date be in the rows and the amount as the data to be added. You can have the dates grouped by year instead of single date, I think. If not, then you will need to have another field with just the year. Let me know if you need better help. Dawn

http://www.calliopeconsulting.net

 

[bilko1]

Try this

Cell A1 5/12/99
Cell B1 =YEAR(A1). This will return only the year
Cell C1 The value you have attributed to that particular date. Eg 2
Cell A2 20/12/99
Cell B2 =YEAR(A2).
Cell C2 Applicable value. Eg 10

Then, wherever you want to return the results for each year use the SUMIF formula making column B your range (instead of column A). So

In cell D5 type 1999
In cell D6 type =SUMIF(B1:B2,D5,C1:C2). Result is 12.

You can then hide column B.

Before anyone shouts at me. Once you've got it all figured out, you should name your ranges in order that you don't keep having to expand the range in your formulas (when you make new entries). Name the entire of Column B "Range" and the entire of column C as "Values". Your formula would then read =SUMIF(Range,D5,Values).

I'm sure there's probably a better way but hope this helps.

 

[CaptainD]

you could try a formula in another area that is hidden that has the following formula =IF(YEAR(C2)=2000,E2,"&quot then drag that down to the cells below so they progress down the column then do a sum of that column. What that would do is show nothing if the year is not 2000 and the amount in E2 if it is, then the next row over do =IF(YEAR(C2)=2001,B2,"&quot
etc. then sum each columns amount. and show that sum in a visible area.

I tried getting a sumif to work but no luck.

 

[dawnd3]

I guess the question is Seth, how do you want to view your results? Do you want to see the totals for all years at once, or just a particular year. You could also do an auto filter and filter for a date range with the sum function at the bottom of the amount column. There are many things you can do. How do you want to view your results and how often do you need the results. Do you want constant show of totals on another sheet or do you just want to filter when you need to see a specific year? Dawn

http://www.calliopeconsulting.net

 

[Hasit]

Best way that I can think of to do this is:

1. In a column (say "D&quot, type in =YEAR(C1) and drag this down to as menay cells there are in column C, so this then extracts all your years from the date entries.

2. Type in this formula: =SUMIF(D14,2000,E1:E4) which will give you the total for each year (middle portion of the formula (i.e. the 2000) is the year you are comparing for).

Let me know if this suits your needs!

 

[xlbo]

As an alternative idea - if you don't want to add columns, an array formula may do the trick - remember however that array formulae rake longer to calculate so if there's gonna be loads of them it may be a better idea to add the extra columns and do it as previously suggested
Array formula for dates in A and values in B:
=SUM((YEAR(A2:A1000)=2000)*(B2:B1000))

This needs to be entered with Ctrl+Shift+Enter - it will add {} brackets round the outside to show this.

HTH
Geoff

 

[SkipVought]

Hi, SethP,

dawnd3 is right! Excel's PivotTable Wizard is a VERY POWERFUL data summary tool. It can do EXACTLY what you are asking for without formulae.

Here are the key factors...

1. Your table must have column heading names
2. Your table must be contiguous (no empty rows or columns)
3. In the PivoeTable Wizard, after you have dragged the date field to either the row of column area, doubleclick the date PivotField and click the Number button - Create a custom format of yyyy to display the year only.

The possibilities are nearly endless with what views a PivotTable you can produce. It boggles the mind (like boneless chicken ) Skip,
metzgsk@voughtaircraft.com

 

[Loomah]

Another suggestion is, using the suggestions above to obtain the year, use sub totals at each change in year. It isn't 'pretty' but will give you the totals you are looking for.

 

[dawnd3]

thanks Skip!

http://www.calliopeconsulting.net

 

[bilko1]

Hi
I was going to suggest a pivot table but didn't b'cos I didn't think you could automatically re-set your pivot data when you added new details to the source data. I know you refresh to update but what if you regularly add to the source (more rows) ??. Wouldn't you have to go back to step 3 of the Wiz every time and increase the range there ?.

 

[xlbo]

You can set a range name as the source data for a PT. You can also create dynamic range names using counta and offset.
Combine the 2 and whaddaya get.....a dynamically expanding pivot table. You still need to refresh it tho ;-)
Geoff

 

[AngO]

xlbo & bilko1
You can set the pivot table to automatically update.
In the third step of the wizard, click on options. In the bottom right area of the screen - refresh on open.
Best Wishes
AngO

 

[SethP]

WOW!
I didn't think I would get this much of a response. I finally finished my report!
I ended up using ideas from a few suggestions. I filtered out the year and I set up a bins_array.
I tried a lot of other suggestions and learned some new things along way.

Thanks to all!
Seth