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

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

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

Where's the sample?

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

oops forgot to upload it :p

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

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

6. ## 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?

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

#### Thread Information

##### Users Browsing this Thread

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