+ Reply to Thread
Results 1 to 11 of 11

Need help with Match, Index or Arrays to generate required data (Sample Attached).

  1. #1
    Registered User
    Join Date
    11-02-2015
    Location
    Saudi Arabia
    MS-Off Ver
    2013
    Posts
    6

    Need help with Match, Index or Arrays to generate required data (Sample Attached).

    Dear Friends,

    I am wondering if someone could help me with the problem I am facing with my data sheet. Actually I am making a Productivity and Man-Days sheet for a construction project and facing problems to generate some data i require from it. I have attached the sample and highlight the columns requiring formula's. Someone please help me with this :/

    Thanks in advance.
    Attached Files Attached Files
    Last edited by Faraz Memon; 01-26-2016 at 02:32 AM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Need some help with creating complex formulae for a table. (Mockup attached).

    Without using VBA, you cannot count cells which are coloured - the working days.

  3. #3
    Registered User
    Join Date
    11-02-2015
    Location
    Saudi Arabia
    MS-Off Ver
    2013
    Posts
    6

    Re: Need some help with creating complex formulae for a table. (Mockup attached).

    I don't have any problem using VBA. Kindly help me with that please.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Need some help with creating complex formulae for a table. (Mockup attached).

    Another problem: you cannot count cells coloured by Conditional Formatting.

  5. #5
    Registered User
    Join Date
    11-02-2015
    Location
    Saudi Arabia
    MS-Off Ver
    2013
    Posts
    6

    Re: Need some help with creating complex formulae for a table. (Mockup attached).

    I tried to use a simple approach for this by using countif formula mentioned below (Row#3). It worked fine.
    =COUNTIF(BZ3:DC3,">=1")

    The real problem I am facing is in columns of actual start date, actual finish date and non work days apart from day off.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Need some help with creating complex formulae for a table. (Mockup attached).

    Working days

    =SUMPRODUCT(--($BZ3:$DC3>0),--($BZ$1:$DC$1>=$DD3),--($BZ$1:$DC$1<=$DE3))

    Definition of non-working days? Is it Total days - Working days? Or are there "weekends" to be considered?

    Calculation of man days?
    Last edited by JohnTopley; 01-24-2016 at 09:46 AM.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Need some help with creating complex formulae for a table. (Mockup attached).

    In the attached find these formulae:

    Array entered in DD3 and filled down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you aren’t familiar with array-entered formulae array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    Array entered in DE3 and filled down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In DF3 entered in the regular way and filled down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In DG3 entered in the regular way and filled down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In DH3 entered in the regular way and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In DI3 entered in the regular way and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    These return the outcomes indicated in the original post upload.
    Attached Files Attached Files
    Dave

  8. #8
    Registered User
    Join Date
    11-02-2015
    Location
    Saudi Arabia
    MS-Off Ver
    2013
    Posts
    6

    Re: Need some help with creating complex formulae for a table. (Mockup attached).

    Oh thank you so much guys for you help. I have checked and the file works perfectly.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need some help with creating complex formulae for a table. (Mockup attached).

    Faraz Memon welcome to the forum

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

    Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (note: this change is not optional )
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  10. #10
    Registered User
    Join Date
    11-02-2015
    Location
    Saudi Arabia
    MS-Off Ver
    2013
    Posts
    6

    Re: Need some help with creating complex formulae for a table. (Mockup attached).

    Quote Originally Posted by FDibbins View Post
    Faraz Memon welcome to the forum

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

    Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (note: this change is not optional )

    I will do this today after understanding the forum rules. Thanks for your guidance.

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Need some help with creating complex formulae for a table. (Mockup attached).

    Glad it works. Thank you for the feedback and rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 01-24-2016, 09:06 AM
  2. How to create a magazine mockup in exce?
    By prashant290 in forum Excel General
    Replies: 5
    Last Post: 08-04-2015, 02:19 AM
  3. Replies: 3
    Last Post: 09-22-2012, 04:21 PM
  4. Creating a Gantt Chart with data attached...
    By Bayebd24 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-26-2012, 05:33 PM
  5. Setting up some VBA to enter details into a complex form (spreadsheet attached!)
    By AdamLord in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-06-2011, 10:57 AM
  6. Complex formulae
    By 3daluminium in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-22-2010, 05:09 AM
  7. Replies: 0
    Last Post: 08-02-2006, 02:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1