+ Reply to Thread
Results 1 to 22 of 22

Wildcard Function in sumproduct

  1. #1
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    Posts
    986

    Question Wildcard Function in sumproduct

    Hi,

    I have a sumproduct function in which I'm trying find a name within a specified date range in my cell G48.

    The intent of this function is to pull task owner or task owners & determine how many tasks assigned.

    I thought this was the wildcard function / finder - Not sure what's this is called:

    "*"&G48&"*"

    Except, it's not working. How do I need to change my function so it if finds a specified name (in cell G48), regardless of if there's text before or after?


    =IF(G48="","",SUMPRODUCT(--('T1 Task Log'!$R$13:$R$570<>'T1 Task Log'!$L$11),--(ISNUMBER(FIND("*"&G48&"*",'T1 Task Log'!$O$13:$O$570)))))

    Thanks
    MyCon
    -- Using Latest Version of Excel

  2. #2
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    Posts
    986

    Re: Wildcard Function in sumproduct

    Hi,

    Can someone please assist. This function is partially working if it meets a specific criteria.

    I'm not sure if there's something missing or incorrect or if there's a better function do the same type of thing.

    As mentioned above, I'm seeking:

    * A name in a Task Ownership column - May be 1 owner or multiple
    * In my data collection table, I have a cell with the individual's name. Now, I'm seeking this person's name within a specified date range & sum the total tasks assigned to this particular individual.
    -- Typically within a weekly duration

    What am I missing or what do I need to modify? Does a Sumif or VLookup work? I would still need to find a single individual / name that may be amongst several others within in the Task Owner(s) column.

    Thanks

  3. #3
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Wildcard Function in sumproduct

    Hi mycon73

    try the following

    =INDEX('T1 Task Log'!$O$13:$O$570,MATCH(1,('T1 Task Log'!$R$13:$R$570<>'T1 Task Log'!$L$11)*ISNUMBER((SEARCH("*"&G48&"*",'T1 Task Log'!$O$13:$O$570))),0))

    or

    =INDEX('T1 Task Log'!$O$13:$O$570,MATCH(1,('T1 Task Log'!$R$13:$R$570<>'T1 Task Log'!$L$11)*ISNUMBER((SEARCH(G48,'T1 Task Log'!$O$13:$O$570))),0))

    Note: it is an array formula and should be confirmed through the Ctrl+Shift+Enter

    kindly upload a sample workbook if the problem is not solved

  4. #4
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    Posts
    986

    Re: Wildcard Function in sumproduct

    Hi Everyone,

    Azam Ali - Your function gave me the person's name that I'm seeking, but not the total number of tasks assigned to this individual, which what I'm seeking.

    Perhaps, my modified Excel 2007 file example will help.

    In one worksheet - Task Log, I have a series of tasks & it's Lead Supervisor(s) or Engineer(s) - Task owners listed in its respective columns

    As you will see on my TATs (Turn Around Time) worksheet, have a couple of tables that's suppose to extract various info. on a weekly & monthly basis.

    My top table - Supervisor Team Metrics is suppose to extract "Team" performances from my lower tables - Only showing 2 tables for now, but will have multiple tables on a weekly basis.

    Right now, the intent in cell L8, I'm seeking to find "Supervisor 7" from my Task Log Worksheet in column O & determine the total uncompleted tasks, on a weekly basis.
    -- We are trying to determine if the person is overloaded

    As you will see "Supervisor 7" also has joint tasks or working with someone else on certain tasks.

    This is where my original function was not working. It was only finding the "single" incident, but not multiple, which is what I'm seeking.

    As you see in the Task Log worksheet, Supervisor 7 / Supervisor 3 are sharing a couple of the assignments.

    This type of function principle will be carried over to other cells for other criteria.

    What am I missing to have the wildcard finder (or whatever its called) to get my function or another variation working?

    Once, I have my weekly individual data tables working, I'll extract this info & putting a more "team" overview & will chart this out in some way.

    Thanks

  5. #5
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    Posts
    986

    Re: Wildcard Function in sumproduct

    Oops...

    I forgot the attachment. Here it is.

    Don't worry about getting the whole table to work. I think once I have the primary function working properly, I can copy that over to get my other criteria totals.

    Thanks

  6. #6
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Wildcard Function in sumproduct

    The find() function does not use wildcards. It already is searching inside a string for a sub-string. Adding * before and after is telling it to litterally find "*yourname*", including the asterisks. Simply remove the asterisks and you should be all set.

    =IF(G48="","",SUMPRODUCT(--('T1 Task Log'!$R$13:$R$570<>'T1 Task Log'!$L$11),--(ISNUMBER(FIND(G48,'T1 Task Log'!$O$13:$O$570)))))

  7. #7
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    Posts
    986

    Re: Wildcard Function in sumproduct

    Hi Whizbang,

    Using:

    =IF(G48="","",SUMPRODUCT(--('T1 Task Log'!$R$13:$R$570<>'T1 Task Log'!$L$11),--(ISNUMBER(FIND(G48,'T1 Task Log'!$O$13:$O$570)))))

    Seeks an exact match & this does work well when there is an exact match. The problem is that in my task log, I may have 2 different individuals sharing the responsibilities.

    Here, is where I need my function modified so it finds the person I'm seeking in my cell G8, regardless if there is one owner or multiple owners or if there's variation of the person's name, such as including his first name.

    I thought the "*"g8"*" or some variation is suppose to work. That's the "wildcard" function that I'm seeking.

    Am I adding too many symbols around my G8 or is there something else?

    Thanks

  8. #8
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Wildcard Function in sumproduct

    I think perhaps the issue you are having is that FIND() is case sensitive, whereas SEARCH() is not. Try:

    =IF(G48="","",SUMPRODUCT(--('T1 Task Log'!$R$13:$R$570<>'T1 Task Log'!$L$11),--(ISNUMBER(SEARCH(G48,'T1 Task Log'!$O$13:$O$570)))))


    FIND and SEARCH look for a partial string. You shouldn't need to wrap them in wildcards.

  9. #9
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    Posts
    986

    Re: Wildcard Function in sumproduct

    Hi Whizbang,

    That was the issue!!! I knew someone here would have some variation that would work for me.

    Thank you.....

  10. #10
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    Posts
    986

    Re: Wildcard Function in sumproduct

    Hi,

    I thought I had this working before, but now it's not. In my column, "# of Tasks
    Due This Week" is suppose to be same principle as my previous question, except now I'm seeking total number of tasks within a specified week for an individual through:

    =IF(G48="","",SUMPRODUCT(--('T1 Task Log'!$Q$13:$Q$570>=I48),--('T1 Task Log'!$Q$13:$Q$570<=J48),--(ISNUMBER(SEARCH(G48,'T1 Task Log'!$O$13:$O$570)))))

    It's not adding up correctly or at all, depending on how I modify the function. As before, there may be multiple task owners & I'm seeking the individual defined in cell G48.

    Any suggestions?

    Thanks

  11. #11
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Wildcard Function in sumproduct

    Hi mycon73

    i find no error with this formula.

    Kindly upload your sample workbook and kindly also mention the result you need with this formula.

  12. #12
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    Posts
    986

    Re: Wildcard Function in sumproduct

    Hi Azam,

    Up top, I do have an example file. The only difference is:

    In Cell L48, the following function that Whizban suggested & works is:

    =IF(G48="","",SUMPRODUCT(--('T1 Task Log'!$R$13:$R$570<>'T1 Task Log'!$L$11),--(ISNUMBER(SEARCH(G48,'T1 Task Log'!$O$13:$O$570)))))

    Now, in cell M48 - # of Tasks Due This Week, I'm trying to extract from my Task Log worksheet with the person's name found in cell G8 between the date ranges specified in cell I48 & J48 to add or total up the number of deliverable or tasks due during this particular week.

    As you will see in my Task Log worksheet, there may be 2 people assigned to the same tasks. This is why I need some type of SEARCH function that will find the person's name specified in cell G48 & is not case sensitive.

    The same type principle is also suppose to be applied in my On-Time, Late columns as well.

    What do you suggest?

    Thanks

  13. #13
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Wildcard Function in sumproduct

    Edit***
    Spoke to soon. BRB with another answer.

    **Edit 2**

    Ok, I get 2 both from the formula you provided and by counting manually. What number are you expecting?
    Last edited by Whizbang; 07-21-2011 at 10:59 AM.

  14. #14
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    Posts
    986

    Re: Wildcard Function in sumproduct

    Hi,

    I've been messing around with these worksheets a little bit, so there may be a slight variation, but I don't think affect the results.

    If looking in my Task Log worksheet, you will see that Supervisors 1 & 2 are assigned to the same tasks, as well as Supervisors 7 & 3.

    Let's say we select / filter Supervisors 7 in my TATs Worksheet. If looking at the Task Log, we will see that Supervisor 7 has a few single tasks assigned, as well as tasks with Supervisor 3

    With the date range 06/20 - 06/25 on the TATs worksheet, I'm seeking functions that will result with:

    In Column:
    Current # of Pending Tasks: 3 (1 that falls within date range & 2 pending - One due on 0712 & the other with no due date
    -- This function (now) seems to work well with your guys assistance

    In Column:
    # of Tasks Due This Week:
    Expecting 1 because it falls within date range

    In Columns:

    On-Time...
    Expecting 1 because it falls within date range

    Late,
    Expecting 0 because there were no late tasks within this specified week.

    -- I may have to change this to expand, open up the date ranges - Perhaps to beginning of the year until current date, so it tracks all "Late" deliverable, instead of being during a specified week duration.

    I figure once I have the 1 row calculating correctly, then I'll just drag them down into other rows.

    I hope this helps.....

    Thanks again everyone for your assistance.

  15. #15
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Wildcard Function in sumproduct

    Quote Originally Posted by mycon73 View Post
    In Column:
    # of Tasks Due This Week:
    Expecting 1 because it falls within date range
    I count two. Rows 21 and 26 on the Task Log sheet. They both fall in the date range and have "Supervisor 7".

  16. #16
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    Posts
    986

    Re: Wildcard Function in sumproduct

    Hi Whizbang,

    You are probably right. As mentioned, I was messing around with my worksheets & there may be a slight variation of results. Regardless, I'm still seeking the same functionalities.

    Thanks

  17. #17
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Wildcard Function in sumproduct

    Please upload an updated workbook so that we can talk apples to apples.

  18. #18
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    Posts
    986

    Re: Wildcard Function in sumproduct

    Hi,

    You are probably right! As mentioned, I was messing around with my worksheets a little bit & therefore, the results may vary.

    However, the intents of functionalities to get the same results still remains the same.

    Thanks

  19. #19
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    Posts
    986

    Re: Wildcard Function in sumproduct

    Our discussions seems to be crossing paths a little bit.

    Here's an (updated file).

  20. #20
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Wildcard Function in sumproduct

    Quote Originally Posted by mycon73 View Post
    In Column:
    # of Tasks Due This Week:
    Expecting 1 because it falls within date range
    I still count two, both manually and using the formula. Task 9 and Task 7 both beet the criteria.

    Quote Originally Posted by mycon73 View Post
    In Columns:

    On-Time...
    Expecting 1 because it falls within date range
    I also count 1

    Quote Originally Posted by mycon73 View Post
    Late,
    Expecting 0 because there were no late tasks within this specified week.
    I count 1. Task 9 meets the criteria.

  21. #21
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    Posts
    986

    Re: Wildcard Function in sumproduct

    Hi Whizbang,

    In some instances, the columns are providing the correct results.

    If reviewing / filtering Supervisor 3 & 7 in the task log & reviewing results in rows 48 & 49 of the TATs worksheet:

    * The Current # of Pending Tasks column works well

    * # of Tasks Column

    Supervisor 3 - I think I should be seeing 0 because it does appear that any tasks are due during this specific week - 06/20 - 06/26

    It's counting 1 & I don't see where it's getting its result

    For Supervisor 7

    * The result is 2 & it looks like it should be 1 because only on tasks falls within date range

    The more I look at this, the more I think I want to change the calculations for On-time & Late calculations.

    Right now, I'm looking for the specific week durations, but I don't think my end chart (that I will have) will be truly representative to the individuals on-time / late deliverable statuses

  22. #22
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    Posts
    986

    Re: Wildcard Function in sumproduct

    Ooops,

    Submitted before I was done...

    - As for the On-Time & Late status, I think I'll open up those date ranges & calculate the planned finish dates vs. actual finish date.

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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