If I need a count of how many days within a range are not saturdays or sundays, why wouldn't this work:
PS: Preferably if I don't have to use ATP formulas
Please Login or Register to view this content.
Thanks
EDIT: Bear in mind that some of the cells in the range may be blank. I gotten away for now given that there weren't many cells in the range with the following, but for educational purposes, I'd still like to learn additional ways:
Please Login or Register to view this content.
Last edited by ron2k_1; 09-04-2011 at 12:56 AM.
Ron
Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad
Kindly
[1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
[2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
[3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated
=sumproduct(--(weekday(am6:aq6,2)<6),--(am6:aq6<>0))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Yeah, I'm getting the #VALUE error with the above due to having some cells with "". I try do away with this, but not happening too.Originally Posted by martindwilson
=sumproduct(--(weekday(am6:aq6,2)<6),--(am6:aq6<>0),--(am6:aq6<>""))
Maybe this
Please Login or Register to view this content.
Last edited by Marcol; 09-02-2011 at 08:13 PM.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
array entered
=SUMPRODUCT(--(IF(ISERROR(WEEKDAY(AM6:AQ6,2)),8,WEEKDAY(AM6:AQ6,2))<6))
Last edited by martindwilson; 09-02-2011 at 08:29 PM.
Seems to work for me, what am I missing?
Both will fail if there is a text entry in the range.
[EDIT]
Missed your last (edited) post Martin, that will handle text entries, but "" was the bogie, not " "
.... Bear in mind that some of the cells in the range may be blank ...
Last edited by Marcol; 09-02-2011 at 09:06 PM.
hmmmmmmm im not so sure
Last edited by martindwilson; 09-02-2011 at 08:53 PM.
Hmmm?
As an array seems also to work if there is a text entry.Please Login or Register to view this content.
Last edited by Marcol; 09-02-2011 at 09:04 PM. Reason: Added attachment
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks