SUMPRODUCT or COUNTIF??

[KirstieA]

All,

I have been working on this for ages and can't get it right, and yet i feel
it should be really simple! Can anyone help?

I have an excel workbook which has 2 worksheets. On worksheet 1 (called
2005Stats) is a table:

ColumnA ColumnB
01/01/2005 Placed
10/01/2005 Placed
15/01/2005 Open
16/02/2005 Closed
23/06/2005 Placed

ColumnA has over 100 records with all different dates of this year, ColumnB
has either "Placed", "Closed" or "Open"

On worksheet 2, called 2005Calcs i want to have calculations for each month
of the year, so for example

JAN PLACED = 2
JAN OPEN = 1
JAN CLOSED = 0

FEB PLACED = 0
FEB OPEN = 0
FEB CLOSED = 1

and so on..........

HOW DO I DO THIS?!!!!!! I have been playing about with countif and
sumproduct for ages and can't get it right, PLEASE PLEASE HELP!


Kirstie

 

[Bob Phillips]

In sheet 2,

A1: Jan
B1: Placed
C1:=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$20,"mmmyyyy")=A1&YEAR(TODAY())),--(She
et1!$B$1:$B$20=B1))

and then repeat down for all combos.

Or use a pivot table.