Hello Gurus!
This is my first post. I've gotten these two formulas and they work great the only problem is now I need them to work together.
I have three columns:
Column A: Day of week (in d/m/y format)
Column B: Hour of Day (in numbers 0-23 (not formatted in Excel as hours)
Column C: Gain/Loss (currency)
The goal is to sum Column C based on it being a specific day of the week (ie: Sunday) AND it being a specific hour # (ie: 0-23)
Here are the formulas that I have for each part:
Sum of Gain/Loss during a specified hour (in this case "23"):
=SUMIF(B2:B1493,23,C2:C1493)
Sum of Gain/Loss during a specific day of week (in this case "Sunday":
=SUMPRODUCT((WEEKDAY(A2:A1493)=1)*(C2:C1493))
Any help would be appreciated.
On a related question it would be nice for the resulting formula also to be able to take it's hour results from REAL time stamps:
Example:
B1: 12:03
B2: 12:46
B3: 1:05
Be able to calculate B1:B3,12 resulting in all hours from 12:00 to 12:59... or B1, B2 etc.
Thanks for any help that you can provide.
Last edited by laferg69; 07-26-2011 at 03:15 AM.
You can easily combine the 2 functions into one:
=SUMPRODUCT((B2:B1493 = 23)*(WEEKDAY(A2:A1493)=1)*(C2:C1493))
I would prefer the following syntax:
=SUMPRODUCT(--(B2:B1493 = 23),--(WEEKDAY(A2:A1493)=1),(C2:C1493))
But shouldn't matter . There are many methods for this fuction.
Regarding the timestamp. When you have the timestamp like this:
26.07.2011 10:18 (which actually has a cell value of 40750,4262488426) and this is in column A you can use:
=SUMPRODUCT(--(HOUR(A2:A1493) = 23),--(WEEKDAY(A2:A1493)=1),(C2:C1493))
Hope this helps
Richard
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks