What Formula To Use

singingsister

Board Regular
Joined
Jun 13, 2006
Messages
145
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?
 

Some videos you may like

Excel Facts

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

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
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
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

singingsister

Board Regular
Joined
Jun 13, 2006
Messages
145
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
Joined
Feb 16, 2002
Messages
8,458

ADVERTISEMENT

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
Joined
Sep 23, 2005
Messages
886
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
Joined
Apr 19, 2005
Messages
23,707
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,058
Messages
5,545,757
Members
410,704
Latest member
Cobber2008
Top