Dear forum members,
I work with a bi-hourly dataset and want to obtain the daily sum on another spreadsheet that does not require me to change the range manually. I have tried the offset function and read a corresponding entry on your forum, but do not seem to go anywhere.
Note that I also use a multiplicator on the daily sum. Attached a sample spreadsheet.
Your help is highly appreciated.
My suggestion would be to change the format of your hourly reports so that the correct date is in each entry (i.e. the date changes at midnight not midday) and use:
=SUMIF(HOURLY!A1:A1000,DAILY!A11,OFFSET(HOURLY!B1:B1000,0,MATCH(DAILY!B$2,HOURLY!C$2:AT$2,0)))
Copy this into B3 of DAILY. Then drag across & down. Should be fine!
Formula:
Please Login or Register to view this content.
Last edited by Glenn Kennedy; 08-12-2014 at 11:09 AM.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Ignore the bit where I was talking about the reformatting of the data, I missed you were multiplying the answers. My formula in post #2 should be ok regardless.
Hello!
@Ragulduy: Thanks for your reply. I had to start at midday for practical reasons, but will follow up on your suggestion later. Unfortunately the formula didnt work even though I erased all the headings.
@Glenn: your suggestion was practical as you told me where to insert the formula (many thanks!!). For some reasons Excel 2007 didn't like the formula. Any ideas how to fix it?
Have a great day!
Sorry, I was testing it in a copy of the table below and didn't correct the row references.
Try:
=SUMIF(HOURLY!$A$1:$A$1000,$A3,OFFSET(HOURLY!$B$1:$B$1000,0,MATCH(DAILY!B$2,HOURLY!$C$2:$AT$2,0)))*0.62
in B3 and copy down/across.
You will need to correct some of the headings (e.g. plan/plant)
I think I have solved your problem ..
Try this in B3
Copy down and across..
Formula:Please Login or Register to view this content.
Don't forget to click *
Seeing that all your column Headers on the Hourly worksheet match the column headers on the Daily worksheet, something simple like this might do what you require. Enter in B3 and copy down and across. If you need more than 1000 rows change the 1000 to whatever you need.
Formula:Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Many thanks for the astute feedback!
Heartfelt thanks to all contributors! After doing some syntax editing Glenn's suggestion worked. Problem solved!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks