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

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

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

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

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

Hi Whizbang,

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

Thank you.....

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

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

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?

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.

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

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

Our discussions seems to be crossing paths a little bit.

Here's an (updated file).

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

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