+ Reply to Thread
Results 1 to 7 of 7

Looking for a formula for a template to pull data under specific criteria

  1. #1
    Registered User
    Join Date
    04-21-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010
    Posts
    61

    Looking for a formula for a template to pull data under specific criteria

    Hi guys,

    Im trying to figure out a way to create a template so that it pulls data from the original report under the criteria I set for it.

    I've attached how my raw data looks like, and I basically need to create a formula on a separate sheet to pull data that took longer than 3 days to be received than the estimate. I know it can be done much simpler with a pivot table but I am trying to create a template that requires minimal amount of work just copying and pasting the raw data and having the numbers get calculated under that criteria.

    Can this be done using a formula or is a pivot table the only way?

    Thanks
    Attached Files Attached Files
    Last edited by albardit18; 08-09-2011 at 02:08 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Looking for a formula for a template to pull data under specific criteria

    Where's the sample?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    04-21-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Looking for a formula for a template to pull data under specific criteria

    oops forgot to upload it :p

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Looking for a formula for a template to pull data under specific criteria

    In sheet1 add helper formula in E4:

    =IF(D4>3,COUNT(E3:E3)+1,"")

    copied down

    then in F4 add formula:

    =MAX(E:E)

    Then in Sheet2 use formula:

    =IF(ROWS($A$1:$A1)>Sheet1!$F$4,"",INDEX(Sheet1!A:A,MATCH(ROWS($A$1:$A1),Sheet1!$E:$E)))

    copied down as far as you need and across if desired.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Looking for a formula for a template to pull data under specific criteria

    Hello albardit18,

    See the attached. Used dynamic range to limit the Lookup range.
    Attached Files Attached Files
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  6. #6
    Registered User
    Join Date
    04-21-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Looking for a formula for a template to pull data under specific criteria

    That's great help guys. I actually forgot to mention that the rows repeat themselves several times with the same exact information but I just want it pulled once in sheet2, is there any way to do that?
    Attached Files Attached Files

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Looking for a formula for a template to pull data under specific criteria

    Change the helper column E4 formula in Sheet1, that I gave to:

    =IF(AND(COUNTIFS(A$4:A4,A4,B$4:B4,B4,C$4:C4,C4)=1,D4>3),COUNT(E$3:E3)+1,"")


    copied down

    The rest remains the same.

+ 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