Hello gurus!
I have been working on this for days and I cannot make it work:
Column A: Date in m/d/y format
Column B: Hours in number format
Column C: Gain in currency format
I have two formulas now.
One to sum gain based on a single weekday. In this case "sunday"
=SUMPRODUCT((WEEKDAY(A2:A1493)=1)*(C2:C1493))
One to sum gain based on a single hour (in this case "23")
=SUMIF(B2:B1493,23,C2:C1493)
My goal is to combine them both so I can see gain based on when it was a certain HOUR AND a certain DAY
Thank you for your help,
mark
I would think this would work...
=SUMPRODUCT(--(WEEKDAY(A2:A1493)=1),--(B2:B1493=23),C2:C1493)
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
Thanks SO much. It works great.
The only problem is that if the formula refers to an empty cell it responds with the "value" error. How can I fix that?
Also, what would the formula be if the hour column was actually in normal units of time like H:M:S, for example: 12:03:02.
I'd like the formula to output the same 0-23 results by rounding to the nearest hour.
This has saved me DAYS! I love this forum.
Mark
Hi Mark,
Could you post a sanitized version of your spreadsheet so we can see the layout and one which show the results you desire?
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
Thanks,
Here is the goal:
Anytime that I extend the formula to empty cells in the same column I get the value message.
I'd like to extend the formula to all cells in all columns so I can paste data in to the worksheet as it is created.
Here it is as simple as i can make it:
Try this "array formula" in H3
=SUM(IF(ISNUMBER($A$2:$A$2000),IF(TEXT($A$2:$A$2000,"ddd")=H$2,IF($B$2:$B$2000=$G3*24, $F$2:$F$2000))))
confirm with CTRL+SHIFT+ENTER and copy down and across, see attached
Last edited by daddylonglegs; 07-28-2011 at 02:02 PM.
Audere est facere
I copy pasted it into H3.
Thanks for the CTR-SHFT-ENTER to copy formulas. That'll save me hours.
Unfortunately when I do that the results seem to shift a cell It seems to show the next day's results. I'm not sure my results will be accurate.
See att:
Thanks!
I think the original results you had were inaccurate - you were using WEEKDAY=1 in the Monday column but Sunday = 1 so you were showing the Sunday results in the Monday column...my version shows the correct days
Audere est facere
Thanks again for all of the help..
Here is the final WS:
ExampleR2.xlsx
The goal:
Column A, B & N to accept all new data entered into the column. (all the way up to 100000+ lines) or infiniti so I won't have to adjust the formulas
Column B to accept numerical data and round to the nearest hour.
When I copied from the worksheet you attached and changed the column references I could not get the grid to display the same results that were showing with your previous formula (that I manually copied 168 times...)
I used this version in S3
=SUM(IF(ISNUMBER($A$2:$A$2000),IF(TEXT($A$2:$A$2000,"ddd")=S$2,IF($C$2:$C$2000=$R3*24, $N$2:$N$2000))))
confirmed with CTRL+SHIFT+ENTER and copied down and across
Does that work for you?
Audere est facere
Some data is being missed look at exampleR2. If you look at AA30 the totals will help us determine if some data is not being counted.
In R2 the total is $2338
In your newest example the total is $1948.09
Which one is right?
mark
Yes you are right, mark....in some circumstances the $R3*24 part wasn't giving an exact integer......so I need a ROUND function to fix that - this version does so
=SUM(IF(ISNUMBER($A$2:$A$2000),IF(TEXT($A$2:$A$2000,"ddd")=S$2,IF($C$2:$C$2000=ROUND($R3*24,0), $N$2:$N$2000))))
total is now $2323.05....still a small discrepancy but that's caused by an error in your formula in W19...change the 55 to 5 and the totals match, see attached
Audere est facere
Thanks for all of the help.
Last edited by laferg69; 07-28-2011 at 06:07 PM.
one more thing.
The totals now match between the two worksheets, dayandhourv3 and exampleR2, but the cells have very different results. Which one is the most accurate?
As per my previous post (post 8 in this thread) - I think you had the days wrong, you had Sunday data on Monday, Monday data on Tuesday etc. so I think the results in my sheet are correct
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks