criteria to filter and extract row data into separate worksheet

[karenc]

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!

 

[Ashish Mathur]

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