Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
criteria to filter and extract row data into separate worksheet
Hi
I have 2 worksheets in a same workbook. Example: Worksheet A - Details of name of products, prices, supplier, month of export and order amount. Details consisting of whole year's transactions. There are more than 1 row of transaction recorded every month. Worksheet B - A table which automatically consolidates the required details from Worksheet A. In this case, how can I formulate / structure the worksheet and cells so that in Worksheet B: - 1.) Cell A2, I can have the automated count of November's transactions as recorded in rows of Worksheet A 2.) Cell A3, I can have the automated count of December's transactions as recorded in rows of Worksheet A 3.) Cell A4, consolidate "order amount" for the product Carrots in the month of November as recorded in Worksheet A 4.) Cell A5, consolidate "order amount" for the product Carrots in the month of December as recorded in Worksheet A In a nutshell, my plan is to just manually update Worksheet A while Worksheet B will automate itself based on the crtierias which I instruct the cells to extract information from. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
criteria to filter and extract row data into separate worksheet
Hi,
For questions 1 and 2, you can use the COUNTIF() function - =countif(range,"November"). Please note that criteria part of the countif() function will depend upn how you have month in worksheet 1 - whether November, Nov etc. For questions 3 and 4, you can use =sumproduct((range1="Carrots")*(range2="November") ,sum_range) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "karenc" wrote in message ... Hi I have 2 worksheets in a same workbook. Example: Worksheet A - Details of name of products, prices, supplier, month of export and order amount. Details consisting of whole year's transactions. There are more than 1 row of transaction recorded every month. Worksheet B - A table which automatically consolidates the required details from Worksheet A. In this case, how can I formulate / structure the worksheet and cells so that in Worksheet B: - 1.) Cell A2, I can have the automated count of November's transactions as recorded in rows of Worksheet A 2.) Cell A3, I can have the automated count of December's transactions as recorded in rows of Worksheet A 3.) Cell A4, consolidate "order amount" for the product Carrots in the month of November as recorded in Worksheet A 4.) Cell A5, consolidate "order amount" for the product Carrots in the month of December as recorded in Worksheet A In a nutshell, my plan is to just manually update Worksheet A while Worksheet B will automate itself based on the crtierias which I instruct the cells to extract information from. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort/Filter Data alphabetically into separate worksheets A-Z | Excel Discussion (Misc queries) | |||
Pull info from separate worksheet based on given criteria | Excel Discussion (Misc queries) | |||
Summing totals on separate worksheet based on 2 criteria | Excel Discussion (Misc queries) | |||
filter data from 10+workbooks and display in separate workbook? | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |