LinkBack URL
About LinkBacks
Hello
I have a list with dates in column A.
In E1 type the start date & in E2 the end date.
I need to count how many Saturdays exist in the list for this period.
Thanks in advance.
Hello
I have a list with dates in column A.
In E1 type the start date & in E2 the end date.
I need to count how many Saturdays exist in the list for this period.
Thanks in advance.
Try
=SUMPRODUCT(--($A$1:$A$73>=$E$1), --($A$1:$A$73<=$E$2), --(WEEKDAY($A$1:$A$73,1)=7))
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Try
=SUMPRODUCT((A1:A73>=E1)*(A1:A73<=E2)*(WEEKDAY(A1:A73)=7))
Life's a spreadsheet, Excel!
Say thanks, Click *
Hi.
=SUMPRODUCT(0+(A1:A73>=E1),0+(A1:A73<=E2),0+(WEEKDAY(A1:A73)=7))
Regards
Hi,
Try below array formula in 2007+
=SUM(--(IFERROR(WEEKDAY(IF(($A$1:$A$73>=E1)*($A$1:$A$73<=E2),$A$1:$A$73,"e"),2),0)=6))
Confirm with Cntl+Shift+Enter.
Thank you all.
May i ask(as i have more than 25000 rows with dates in column A) if we can do this using some other function than SUMPRODUCT, that as i learned here is-in fact- an ARRAY formula?
Hmm..Use a helper column perhaps?
=--AND(A1>=$E$1,A1<=$E$2,WEEKDAY(A1)=7)
Copy down, then sum this column
@misrasomendra
Ah! So you use this array construction with IFERROR and "e" in all your formulas!
Very original!(Though of course a CSE formula is not necessary here, and I have to add that we really should try to avoid IFERROR if possible. Still, points for creativity!)
Regards
@XOR LX,
Can you believe when I tested all the four formula above on the given data set, mine is fastest
Regards,
try this..
Formula:
Please Login or Register to view this content.
Regards,
Vikas Gautam
Excel-buzz.blogspot.com
Excel is not a matter of Experience, its a matter of Application.
Say Thanks, Click * Add Reputation
Actually, no! I can't believe that! How much faster?Originally Posted by misrasomendra
@XOR LX,
Can you believe when I tested all the four formula above on the given data set, mine is fastest
Regards,
Can you post the details of your testing?
Regards
See the file. Time are in Seconds.
Regards,
Thanks. I stand corrected. Interesting.
I still think that the minute difference in speed does not quite make up for the lack in comprehensibility, nor for the necessary use of an IFERROR.
Still, I'd be curious to know of the underlying technical reasons why this version slightly outperforms the alternatives.
And I suppose if we were talking of a situation concerning hundreds of thousands of rows then it may be worth considering.
Regards
If the dates are always sequential as in the example,
=SUM(INT((WEEKDAY(E1 - {7}) + E2 - E1)/7))
The {7} can be expanded to include additional days, e.g., for Wednesdays and Saturdays,
=SUM(INT((WEEKDAY(E1 - {4,7}) + E2 - E1)/7))
Complements of DLL.
Last edited by shg; 10-28-2014 at 10:35 AM.
Entia non sunt multiplicanda sine necessitate
@XOR LX
This was for fun, I came up with this technique while using it with FREQUENCY function. I don't remember the problem or day, but from that day I always used it.
Although, I understand that over the data set of Million or more rows, results might not be same. I had seen other situations where An array formula results in lesser time as compared to non-array solution.
Regards,
Hi Somendra,
By saying the above quoted lines, you are saying that using Nested Ifs is more faster than using Array Multiplication..Can you believe when I tested all the four formula above on the given data set, mine is fastest
But our expert Mr. TMS or TONY VOLKO ( I Don't remember ) has proved me, using the codes below, that using Array multiplication is more
faster... so here are my questions..
How did you tested the speed ...?
Did you use the code in the link below..?
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
However, if this is faster
Formula:Please Login or Register to view this content.
Then this can be more faster..( Arrayed formula )
Formula:Please Login or Register to view this content.
Waiting for reply..
Regards,
Last edited by Vikas_Gautam; 10-27-2014 at 10:31 PM.
@Vikas_Gautam,
Yes I did use the code in the link that you mentioned and Not once but at least 6 times on each formula.
Regards,
Thank you ALL!
Hi,
a possible (late) approach
Formula:Please Login or Register to view this content.
Cheers
edit:weekday =1 (Sundays)
Last edited by canapone; 10-28-2014 at 04:28 AM.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
@canapone
Doesn't it also have to meet the condition of being within the range A1:A73?
Regards
Hi,
good point: sorry I've totally misunderstood the OP needs. I was considering A1:A73 as an helper range.
Not sure the following could be a robust formula:
Formula:Please Login or Register to view this content.
Regards
Late Edit: Weekday = 1
Last edited by canapone; 10-28-2014 at 04:28 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks