What Formula To Use

[singingsister]

I have a long sheet which monitors when certain documents are sent, returned etc which is listed by company. I have a stats sheet at the front and I want to find a formula that will look for a certain company together with a certain word (i.e sent/returned) to give me a total (ie MacDonalds 12 sent, 4 returned).

How do I do this?

 

[Airfix9]

Hi singingsister,

Can you post the relevant part of your spreadsheets here using HTML maker (link below)?

http://www.mrexcel.com/board2/viewtopic.php?t=92622

 

[singingsister]

I'm afraid I'm not able to download any files to my work PC so I can't.

 

[Airfix9]

Hmm,

A tricky one, then. Can you explain the layout of the stats sheet, then? And where you want to place this search?

Ta

 

[Richard Schollar]

Can you describe in more detail the format of your data? Please provide a few exmple rows.

 

[singingsister]

The columns of the stats are as follows:-
Supplier Total Open Closed Rejected % Closed
macdonalds
burger king
wimpy

and the log sheet has:-
Supplier Status (i.e Open/Closed/Rejected)

the suppliers on the log sheet are not in supplier order they are in date order not listed by suppliers as new records are added reguarly. I want the formula to say if column a (supplier) on log sheet is Macdonalds AND column B (status) says open/closed, to count now many there are.

Does this help?

 

[Brian from Maui]

The columns of the stats are as follows:-
Supplier Total Open Closed Rejected % Closed
macdonalds
burger king
wimpy

and the log sheet has:-
Supplier Status (i.e Open/Closed/Rejected)

the suppliers on the log sheet are not in supplier order they are in date order not listed by suppliers as new records are added reguarly. I want the formula to say if column a (supplier) on log sheet is Macdonalds AND column B (status) says open/closed, to count now many there are.

Does this help?

This is just a guess, but it seems you want a multi conditional count.

A generic approach,

=SUMPRODUCT(--(SupplierRange="Macdonalds"),--(StatusRange="Open"))

 

[Airfix9]

OK, I assume the log sheet to look like this:

Book1
	A	B	C	D
1	Supplier	Open	Closed	Rejected
2	Wimpy	0	1	0
3	BurgerKing	1	1	1
4	Wimpy	0	0	0
5	Wimpy	1	1	0
6	McDonalds	1	1	0
7	Wimpy	0	1	0
8	Wimpy	1	1	1
9	BurgerKing	0	1	0
10	Wimpy	0	1	1
11	Wimpy	0	1	0
12	BurgerKing	1	0	1
13	Wimpy	1	0	0
14	Wimpy	1	1	1
15	BurgerKing	1	0	0
16	Wimpy	1	0	0
17	BurgerKing	0	0	0
18	McDonalds	1	0	0
19	BurgerKing	0	1	0
20	Wimpy	0	0	1
21	Wimpy	1	0	1
22	BurgerKing	0	1	1
23	BurgerKing	1	0	1
24	Wimpy	1	1	0
25	Wimpy	0	0	1
Log

My Stats sheet looks like this:

Book1
	A	B	C	D	E	F	G
1	Supplier	Total	Open	Closed	Rejected	%	Closed
2	BurgerKing	12	4	4	4
3	McDonalds	3	2	1	0
4	Wimpy	21	7	8	6
Stats

 

[Richard Schollar]

To add to what Brian said, here's a graphical example:

Book2
	A	B	C	D	E	F	G	H
1	Name	Date	Status	Name	Open	Closed	Rejected	Total
2	BurgerKing	01/01/2006	Open	BurgerKing	6	2	1	9
3	McDonald's	01/01/2006	Open	McDonald's	5	2	0	7
4	Wimpy	01/01/2006	Closed	Wimpy	0	3	2	5
5	KFC	01/01/2006	Open	KFC	4	0	1	5
6	BurgerKing	01/02/2006	Closed	Total	15	7	4	26
7	McDonald's	01/02/2006	Open
8	Wimpy	01/02/2006	Rejected
9	KFC	01/02/2006	Open
10	BurgerKing	01/02/2006	Open
11	BurgerKing	08/06/2006	Open
12	BurgerKing	02/03/2006	Open
13	Wimpy	08/06/2006	Closed
14	KFC	08/06/2006	Open
15	BurgerKing	01/07/2006	Closed
16	McDonald's	01/07/2006	Open
17	Wimpy	01/07/2006	Rejected
18	KFC	01/07/2006	Rejected
19	McDonald's	01/07/2006	Closed
20	McDonald's	01/11/2006	Open
21	McDonald's	01/04/2006	Closed
22	McDonald's	29/01/2006	Open
23	Wimpy	30/01/2006	Closed
24	KFC	31/01/2006	Open
25			26
Sheet1

This is only an examle, and would need to be adjusted to suit your needs/ranges/sheets.

Does it help?

 

[singingsister]

it kinda helps. I'll see if sumproduct works....