MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
Some help from the hive if possible please!?
I have a list of dates with corresponding values. sometimes there are a number of entries on the same date with different values (maybe 20-30 entries). I want to know the sum and average of the values based on each date. I was going to give the spreadsheet to our receptionist and leave her for a day or so to do this by hand, but there must be an easier way in excel. Lookup and sum or lookup and average.
SUMIF and COUNTIF are the basic tools you can use. Have a practice and you should be able to work it out v quickly.
For more advanced users SUMPRODUCT would be a slicker solution.
Simple(ish) long hand way would be countif
Copy all your dates to somewhere else (make sure they are date format with no time) paste & remove duplicates
Paste the new list into a row above the other list (Paste Special Transpose)
So they now sit in Row 1, Dates in Row 2 Col A, Values Row 2 Col B
Under each date eg C2=IF(C$2=$A2,B2,"")
Drag that down then across each row then either on that sheet or elsewhere SUM(C2:CNumber) or Avg
sure there is a simpler way
perfect, Stoners solution is within my brain power and works a treat, Thanks
Pivot table seconded
those pivot tables look like a good idea for next time.. 😀
Please use a pivot table. they are perfect for what you want.
Its dead easy to summarise by
count
sum
average
max
min
here is an example:
[url= https://dl.dropboxusercontent.com/u/438710/pivttableexample.xlsx ]https://dl.dropboxusercontent.com/u/438710/pivttableexample.xlsx[/url]
@sum( . )
HTHs.