What Formula To Use

singingsister

Board Regular
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?

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

singingsister

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

Airfix9

Well-known Member
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

MrExcel MVP

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

singingsister

Board Regular
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

MrExcel MVP

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

Well-known Member
OK, I assume the log sheet to look like this:
Book1
ABCD
1SupplierOpenClosedRejected
2Wimpy010
3BurgerKing111
4Wimpy000
5Wimpy110
6McDonalds110
7Wimpy010
8Wimpy111
9BurgerKing010
10Wimpy011
11Wimpy010
12BurgerKing101
13Wimpy100
14Wimpy111
15BurgerKing100
16Wimpy100
17BurgerKing000
18McDonalds100
19BurgerKing010
20Wimpy001
21Wimpy101
22BurgerKing011
23BurgerKing101
24Wimpy110
25Wimpy001
Log

My Stats sheet looks like this:
Book1
ABCDEFG
1SupplierTotalOpenClosedRejected%Closed
2BurgerKing12444
3McDonalds3210
4Wimpy21786
Stats

Richard Schollar

MrExcel MVP
To add to what Brian said, here's a graphical example:
Book2
ABCDEFGH
1NameDateStatusNameOpenClosedRejectedTotal
2BurgerKing01/01/2006OpenBurgerKing6219
3McDonald's01/01/2006OpenMcDonald's5207
4Wimpy01/01/2006ClosedWimpy0325
5KFC01/01/2006OpenKFC4015
6BurgerKing01/02/2006ClosedTotal157426
7McDonald's01/02/2006Open
8Wimpy01/02/2006Rejected
9KFC01/02/2006Open
10BurgerKing01/02/2006Open
11BurgerKing08/06/2006Open
12BurgerKing02/03/2006Open
13Wimpy08/06/2006Closed
14KFC08/06/2006Open
15BurgerKing01/07/2006Closed
16McDonald's01/07/2006Open
17Wimpy01/07/2006Rejected
18KFC01/07/2006Rejected
19McDonald's01/07/2006Closed
20McDonald's01/11/2006Open
21McDonald's01/04/2006Closed
22McDonald's29/01/2006Open
23Wimpy30/01/2006Closed
24KFC31/01/2006Open
2526
Sheet1

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

Does it help?

singingsister

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

Replies
25
Views
375
Replies
9
Views
102
Replies
1
Views
81
Replies
3
Views
69
Replies
0
Views
40