counting question

[susanna]

hi everyone...
i hope someone can help. I have a large spreadsheet that i download on one tab (tab A) which contains administrator name in column A, channel in column B, and column F which is called "contract signed" and is populated with dates (3-Jun-02, 4-Jun-02, etc.) On another tab (tab B), i have a table. In column A all administrator names, in column B the channel they are in and columns C-Q, row 5 are dates (6/3, 6/4 etc.) What i need is a formula to put into the table on tab B that will count how many orders were contract signed on 6/3 by the administrator listed in column A in the channel listed in column B.

Any ideas? I am having a hard time here at work downloading the thing that lets you put spreadsheets on the board... if i am making no sense, let me know and i will try posting from home.

thanks as usual.

kindly,
susanna

 

[lenze]

You could us one of the DFUNCTION(See Help File) such As DCOUNT. But, it sounds like you table is ideal for a Pivot Table Report.

Select a cell on the table in tab A. Choose Data>pivot Table Report. Follow the wizard, selecting new sheet for location. When the table grid is dosplayed, drag Administrator and Channel to the row fields, Date to the Column field and date to the Data field.

Should look like the table you described for Tab B.

HTH: Give it a try.

edit: Added example of Pivot Table. The Yellow area is a Pivot table based on the data in Col A:C

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	ADM	CHANNEL	DATE		Count of DATE		DATE
2	Jones	A	7-Oct		ADM	CHANNEL	5-Oct	7-Oct	8-Oct	9-Oct	11-Oct	Grand Total
3	Smith	B	7-Oct		Brown	B				1		1
4	Brown	C	5-Oct			C	1					1
5	Jones	A	8-Oct		Jones	A		2	1			3
6	Brown	B	9-Oct		Smith	B		1	1			2
7	Taylor	D	11-Oct		Taylor	D					1	1
8	Jones	A	7-Oct		Grand Total		1	3	2	1	1	8
9	Smith	B	8-Oct
...

This message was edited by lenze on 2002-10-11 15:34

 

[Jay Petrulis]

Hi,

You can use the SUMPRODUCT function to do multiple searches.

Book1
	A	B	C	D	E	F	G	H	I
1	Administrator	Channel	Contractsigned
2	Jay	A	1/1/2002
3	Susanna	B	1/2/2002
4	JohnDoe	C	1/3/2002
5	JaneDoe	D	1/4/2002
6	JohnDoe	C	1/5/2002
7	JaneDoe	D	1/6/2002
8	JillDoe	E	1/7/2002
9
10
11
12
13			1/1/2002	1/2/2002	1/3/2002	1/4/2002	1/5/2002	1/6/2002	1/7/2002
14	Jay	A	1	0	0	0	0	0	0
15	Susanna	B	0	1	0	0	0	0	0
16	JohnDoe	C	0	0	1	0	1	0	0
17	JaneDoe	D	0	0	0	1	0	1	0
18	JillDoe	E	0	0	0	0	0	0	1
19
Sheet1

 

[susanna]

thanks!!! both suggestions worked great!
you guys are the best... life savers as always!

kindly,
susan