# Office 365 >  > [SOLVED] extraction data from excel spreadsheet

## John105183

Hi   I have a general understanding of excel, but now wish to filter out data from a worksheet to post to another part of the same sheet , the criteria being that I need to show from a list of orders  , the number of frequency, over 5 that a certain part number  has been purchased and were the quantity on those purchase's exceeded 10  and the sum of the total quantity ordered , because of the large number of part numbers  available  typing that into a formulae is not practical because its time consuming, is there an efficient way of doing this , any help would be  gratefully appreciated   :Smilie:

----------


## Richard Buttrey

Hi and welcome to the forum.

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

----------


## John105183

HI Richard thanks very much for the quick response to my post , I have uploaded the sample spreadsheet so you can get an idea of what I am trying to achieve,  my Knowledge of excel is limited for the moment but I have tried various ideas , but have been unable to get the result by an efficient formulae.
Thanks once again for your efforts.  :Smilie:

----------


## mike7952

Here is a vba solution




```
Please Login or Register  to view this content.
```

----------


## FDibbins

Do you have the list of part numbers, or does that need to be extracted too?

Also, would it be OK to just list all parts, with their counts and amounts, and either just use that list, or extract from that?

----------


## FDibbins

Assuming you have that list of parts, then this will pull out the sum of all orders placed more than 5 times, and that have more than 10 ordered (it differs from your sample total)
=IF(COUNTIFS($C$3:$C$76,B81)>=5,SUMIFS($F$3:$F$76,$C$3:$C$76,B81,$D$3:$D$76,">=10"),"")
and for the count...
=IF(COUNTIFS($C$3:$C$76,B81)>=5,COUNTIFS($C$3:$C$76,B81,$D$3:$D$76,">=10"),"")

----------


## mike7952

@FDibbins
My understanding of what the OP is wanting is if the order count > 5 with order qty > 10 then sum the Total amount ordered. And not just Where the sum of order count > 5 with order qty > 10. Idk I could be wrong.





> If possible also if the total order value of the same part numbers  could be incorportated into the  same  formulae that would be great but this is not  a priority at this stage.






```
Please Login or Register  to view this content.
```

----------


## FDibbins

I looked at it that way too, and you could be right.  Then the formula becomes...
=IF(COUNTIFS($C$3:$C$76,B81)>=5,SUMIFS($F$3:$F$76,$C$3:$C$76,B81),"")

----------


## mike7952

I know when I wrote my code I came up with the same values your first formulas produced.

----------


## John105183

Hi Guys thanks for all you input, I have perhaps not explained part of the issue correctly as most of the suggestions rely on reference to the part numbers at the bottom of the sample worksheet which I search through and entered manually, I need to extract these also using the same criteria i.e appearing on the list=>5 and order Qty =>10 , them all you suggestions do work thanks

----------


## mike7952

If you can use a vba solution then my solution in post 4 doesn't rely on your part numbers at the bottom.

----------


## John105183

Hi Mike
Although I am not familiar with VBA and your solution doesn't rely on part numbers  posted manually ,  will it still print the part number that fits the criteria then print it  in result like at the bottom of my sample sheet so I can recognise which part number fits the criteria, if so I  am willing to learn VBA many thanks again

----------


## mike7952

Yes it will do what you want. If it doesn't we can modify the code

----------


## M. Shahbaz

Sir please help me, ,,, 
And solve my problem,
Approve my thread 
Thanks,

----------

