LinkBack URL
About LinkBacks
in column A i have a range of dates in 04/10/12 format. is there a nonvba code that can count the number of mondays in that list? i am able to do this with using helper columns but i am more looking for a standalone fix. is this possible?
thank you
in column A i have a range of dates in 04/10/12 format. is there a nonvba code that can count the number of mondays in that list? i am able to do this with using helper columns but i am more looking for a standalone fix. is this possible?
thank you
Last edited by kamelkid2; 04-10-2012 at 10:07 AM.
Are there duplicate dates that you want to count as 1 monday, ie. April 9th appears 5 times in the column?
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
no im looking for instances. april 9 listed 5 times will count as 5, so i am counting repeating dates. sorry i did not clarify
I dont think its possible without helper columns.
Also, when you say nonvba code, you are contradicting the statement.VBA= code.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Hi
Try this
Hope, that helps you.
Please Login or Register to view this content.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Hi,
Try this Attachment 149631
If I was able to help – PLEASE DO NOT FORGET to Click the small star icon at the bottom left of my post.
Thanks,
Bonny Tycoon
i am sorry i return a #value error. are you getting this on your end as well? i have tried as array and regular entryOriginally Posted by Fotis1991
Why?
Take a look to the example, pls.
SUMPRODUCT, is an Array formula, but don;t need to use CSE. Just enter.
oops i forgot about my header column. sorry to waste your time on that. thank you very much this is exactly what i was looking forWhy?
Take a look to the example, pls.
SUMPRODUCT, is an Array formula, but don;t need to use CSE. Just enter.
well now i have a new issue. i set the function to look down to a1000 is it will be a large list that will be populated in the next few weeks. in this example, all of the blank cells are returning as a saturday. is there a way to alleviate this?
nevermind i used =SUMPRODUCT(--(WEEKDAY(log!D$2:D$10000)=7))-SUMPRODUCT(--(COUNTIF(log!D$2:D$10000,""))). this returns correct values. thank you all!
Note that you can use TEXT function, too........and it won't care if you include headers, e.g.
=SUMPRODUCT(--(TEXT(A1:A1000,"ddd")="mon"))
......and to exclude blanks from Saturday count
=SUMPRODUCT((TEXT(A1:A1000,"ddd")="sat")*(A1:A1000<>""))
Audere est facere
so this brings me to another question from here. now that i can count the number of individual weekdays in the list of dates, how could i go about counting the corresponding cells in column H that contain text?
i have tried
but this returns an incorrect zero. im sure this is incorrect syntax as i dont fully understand the sumproduct yet and am getting a touch over my head but this is the last thing that this spreadsheet needs so im just trying to shoehorn anything in that works=COUNTIFS(log!D:D,SUMPRODUCT(--(WEEKDAY(log!D$2:D$10000)=2)),log!H:H,"*")
What kind of text?
This would count rows where column D is a Monday and column H is not blank
=SUMPRODUCT((WEEKDAY(log!D$2:D$10000)=2)*(log!H$2:H$10000<>""))
or specifically text rather than number
=SUMPRODUCT((WEEKDAY(log!D$2:D$10000)=2)*ISTEXT(log!H$2:H$10000))
@ddl
Keep, teaching us(post#12)!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks