# Filter Entries to look for certain criteria

1. ## Filter Entries to look for certain criteria

Hello,

I am looking for someone to create a code for me that will filter for 2 criteria: be between a time interval (July 19,2011 - july 19, 2012) , and match a list of pump #'s.

First i need it to create an array for the pump list.
Then it has to look through 3 worksheets " North , south , and east". It is looking for dates that are inbetween a certain time interval ( that will be input in a worksheet named "MTBR", then compare to the pump array and if the pump #'s match, failure count goes up by 1 (populated in cell g2 for each worksheet).

it would be greatly appreciated to have someone assist me
I will attach the workbook so its easier to understand.

2. ## Re: Filter Entries to look for certain criteria

Hello,

I am not sure I am following you correctly. Are you looking to sum between date period? try SUMIF.

In 'MTBR' sheet B6,

=SUMIF(North!D:D,">="&C\$3,North!G:G)-SUMIF(North!D:D,">"&C\$4,North!G:G)

then copy down, change North to other sheet names in B7:B8

3. ## Re: Filter Entries to look for certain criteria

I tried the sumif in the colum you told me and it gave me nothing.

What i'm trying to do is get a macro to look at those 3 worksheets: find equipment #'s that match the equipment #'s in "sheet1", AND that were scheduled between the dates (that will be input in the MTBR sheet, so they will vary). So for example: if there is a pump "P-2304" and the job to complete it was July 12, 2012. If that pump # is in the list in "sheet1" and happened between the two dates specified in "MTBR" sheet (January 1, 2012 - December 31, 2012), it will count it as 1 failure, once it has gone through a worksheet it will populate the total in the MTBR sheet column C6-C9.

I hope that makes sense now, reference the workbook as you need, this is not about cost sums its just trying to figure out what important pumps in sheet1 are failing.

thanks!

4. ## Re: Filter Entries to look for certain criteria

OK, try this in C6,

=SUMPRODUCT(ISNUMBER(MATCH(North!C\$3:C\$22,NorthEquip,0))*(North!D\$3:D\$22>=C\$3)*(North!D\$3:D\$22<=C\$4))

Defined a name for Sheet1!A2:A153 as NorthEquip. Change the reference in C7:C8

If Master sheet has all the combined (or Input) data

=SUMPRODUCT(ISNUMBER(MATCH(Master!C\$19:C\$31,NorthEquip,0))*(Master!A\$19:A\$31="North")*(Master!D\$19:D\$31>=C\$3)*(Master!D\$19:D\$31<=C\$4))

Adjust the ranges & "North" to "South", "West". Better to use dynamic range.

5. ## Re: Filter Entries to look for certain criteria

Hello,
thanks for all your work but these functions are giving me zero's. I have tried to modify them to work but it is just not occuring. Did you test them yourself on the workbook? Cause I can't figure out whats wrong with them.

thanks

6. ## Re: Filter Entries to look for certain criteria

All the dates in North, South & East are in 2010. The Start & End date in MTBR sheet is from 2011. I changed the Start Date to Jul/1/2010

Also make sure the calculation mode is Automatic.

7. ## Re: Filter Entries to look for certain criteria

Hello,

I have opened the workbook you edited and i changed stuff around and it seems to be working just greatly! The only problem now is when I try to change the ranges for example "=SUMPRODUCT(ISNUMBER(MATCH(South!C\$3:C\$25 (to the entire column C instead of 3-25) ,SouthEquip,0))*(South!D\$3:D\$25(to entire column D instead of 3-25)>=C\$3)*(South!D\$3:D\$25(to entire column d instead of 3-25)<=C\$4) ) .. When i do that it gives me an "#N/A" error. This workbook will have entried added to it once every 4 months, so I need this function to calculate the new additions, which means looking at the entire column.

What i tried to do was just write "=SUMPRODUCT(ISNUMBER(MATCH(South!C:C,SouthEquip,0))*(South!D:D>=C\$3)*South!D:D<=C\$4)), and i got the error message.

hope that makes sense.

and i appreciate your assistance once again, it is marvelous!

8. ## Re: Filter Entries to look for certain criteria

You can use Dynamic Range instead A:A so whenever Add/delete entries will update it. I have used some dynamic range for North, South & East. whenever insert a new date in column D will include until that cell. Same as equipment. whenever add/delete equipment in appropriate locations, will include it.

Assuming equipment will always be text NOT a VALID numbers. alpha-numeric characters is not a problem.

9. ## Re: Filter Entries to look for certain criteria

Hello HaseebA,

I am just curious about the functions you gave me in this thread. "=SUMPRODUCT(ISNUMBER(MATCH(INDEX(SouthData,0,1),SouthEquip,0))*(INDEX(SouthData,0,2)>=C\$3)*(INDEX(SouthData,0,2)<=C\$4) )".. instead of calling this column "Sheet1!C:C", when it was looking to match it up, how did you get it to be called a different name and yet still identify that it was this range it was looking at.

thanks!

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

#### 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