Hi,
I have data as in the attachment. Sheet1 contains Name, Start Date, Finish Date. Sheet2 is used to count the number of start date, finish date by day of the week.
i.e: i would like to count how many start in 1st day of week1, 2nd of week1, 3rd of week1,.... how many finish in 1st day of week1, 2nd day of week1, 3rd of week1,.........
Could you please advice? Your comments will be very much appreciated.
Last edited by sanlen; 01-27-2011 at 09:07 AM.
Try:
=COUNTIF(Sheet1!$B$2:$B$8,LOOKUP(9.9999E+307,$B$1:B$1)+B$3-1)
and
=COUNTIF(Sheet1!$C$2:$C$8,LOOKUP(9.9999E+307,$B$1:B$1)+B$3-1)
copied across.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Hi,
Would you mind to apply this to my attachment? i tried them and look like i got all TRUE which i cannot figure it out.
Sorry for such inconvenience.
Sure....here it is.
Hi,
It is working now. However, what is the term of 9.9999E+307 for? could it be some other number? Whay do we have to use this? I have been trying google this term, but huge of information come up.
That is about the largest number that Excel recognizes, so it is commonly used in conjuction with LOOKUP to find last numerical entry in a range.
LOOKUP() looks for the last time an entry is made that is smaller than or equal to the lookup value... in this case 9.999999e+307 (if you google BigNum, you may get some results).
So what the formula is doing with this: LOOKUP(9.9999E+307,$B$1:B$1)
is looking from B1 to B1 and finding the last numeric entry (i.e. the last date entered) as the formula gets copied across the first $B$1 remains the same (remember absolute vs. relative referencing) and the second B$1 changes to C$1 and then to D$1 and so on, gradually expanding the range... But the last number in the ranges remains the date entered in B1, until we get to column I, then the last date entered is the one in I1, so now the formula takes on that date.. and then completes the task by adding the number in row 3 and subtracting 1 to get the cumulate date number we are looking for. Then COUNTIF checks the range in Sheet1 for matches.
Hope that explains it.
Hi,
Once again, thanks you very much for everything.
I now have another problem. On the same formatting of my heading of sheet2 in the attachment, i need to have 52 weeks for the whole year tracking which mean that i require at least 52x7=364 columns in my tracking sheet.
However, i can get only 256 columns (IV) in excel which i can have only up to week32.
I would like to have up to week52, could you please advice what should i do with my current formatting?
You could transpose it so that you have the week days in column A, Week Num in column B and Day in Column C...
Select A1:V5 and copy it, then go to Sheet3, A1 and go to Edit|Paste Special and select Transpose.
Now in D2, replace the formula with:
=COUNTIF(Sheet1!$B$2:$B$8,LOOKUP(9.9999E+307,$A$2:$A2)+$C2-1)
copied down
and in E2, replace with
=COUNTIF(Sheet1!$C$2:$C$8,LOOKUP(9.9999E+307,$A$2:$A2)+$C2-1)
Hi,
Thanks you very much.
I am trying to automatically highlight (fill color) on the row of current date, however, i've got no idear of how to get this done.
i.e: if today is day 3 of week4 then that row should be hightlighted on red.
Could you please advice?
Please start new threads when you have new questions.
Post a sample with it as we are not sure if you are talking about the original data or the "transposed" data (if you went with that).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks