Column 1(date) Column 2 (values): how to find corresponding max/sum value based on date

[Amimo]

Hi guys,

I have a simple table with 2 columns: left column is "date and time" and the right column is "number". I need to use a function to look at the left column and sees if they are all in 1 date then sums the corresponding numbers in the right column and put it in 3rd column. I have 4661 rows for 13/11/2012 to 23/11/2012 and finally I need to end up with just 11 rows for each date and a corresponding number (sum of all numbers for that specific date)

The problems are:
1- some days have more data than the other days.
2- the left column is a cobmination of date and time for example this is one of them: 23/11/2012 08:46:43

I've tried sumproduct but it has been unsuccessful yet :-/

appreciate it if any one can give me a hand.

Thanks,

[TheExcelphile]

Hi Amimo!

Welcome to the forum!

Here is your solution...Copy of Sumproduct.xlsx

Don't forget to click the little star to the left of this post if you feel I helped!

[Amimo]

Thanks a lot man,

Actually it gives me "Value" error while I just click on the calculation (I did not change any thing though!)
What if I need the maximum? I mean in 1 month I need the maximum value.

Thanks for your help buddy. Much appreciate it

[Fotis1991]

In C2 and copy down.

=TEXT(A2,"d/m/yyyy")

In E2 and copy until E12, this Array Formula.

=INDEX($C$2:$C$4661,MATCH(0,COUNTIF($E$1:E1,$C$2:$C$4661),0)

In F2 and copy to F12.

=SUMPRODUCT(($C$2:$C$4661=E2)*($B$2:$B$4661))


Read here for Arrays formulas.

http://www.cpearson.com/excel/ArrayFormulas.aspx

[Marcol]

With Sheet2
For max on the day,in F2,Drag Down

Please Login or Register  to view this content.

For the max in the month,in H2,Drag Down

Please Login or Register  to view this content.

The latter formula references the year as well as the month.
With your example all the dates in Column C are for the same month and year, so only one value will be returned.

[jeffreybrown]

We would love to continue to help you with your query, but first, before we can proceed, please see the forum rules about proper thread titles and adjust accordingly...

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[Amimo]

Thanks Fotis 1991 and Marcol for your kind help. Really appreciate it.
Jeffeybrown: Thanks buddy for the heads-up. Gonna change the title asap.

I am so impressed by your help guys...