Suppose I have data that looks like:

1/1/2008 1/3/2008 2/7/2008
Ordered Ordered Ordered
Aardvarks 8 7 3
Baboons 3 10 7
Cats 5 3 2
Ducks 5 1 7
Elephants 7 2 8

I want to get, by month, the total of animals on another sheet

January February
Ordered Ordered
Ducks 6 7
Aardvarks 15 3
Elephants 9 8
Baboons 13 7
Cats 8 2

The tricky part is that I can't control what rows will go where. The two
lists sorting order will change, and new items might be added at any time
(say the zoo wants to add Flamingoes and Giraffes)

That is, for the first sheet, you might see Aardvarks, Baboons, Cats, Ducks,
Elephants, but you could also see Baboons, Elephants, Cats, Aardvarks, Ducks.

Likewise for the second sheet; the rows could be in any order.

Any suggestions?

Is there anything I can clarify to make answering the question easier?

Help?

Jim

--

"jiml" wrote:

Suppose I have data that looks like:

1/1/2008 1/3/2008 2/7/2008
Ordered Ordered Ordered
Aardvarks 8 7 3
Baboons 3 10 7
Cats 5 3 2
Ducks 5 1 7
Elephants 7 2 8

I want to get, by month, the total of animals on another sheet

January February
Ordered Ordered
Ducks 6 7
Aardvarks 15 3
Elephants 9 8
Baboons 13 7
Cats 8 2

The tricky part is that I can't control what rows will go where. The two
lists sorting order will change, and new items might be added at any time
(say the zoo wants to add Flamingoes and Giraffes)

That is, for the first sheet, you might see Aardvarks, Baboons, Cats, Ducks,
Elephants, but you could also see Baboons, Elephants, Cats, Aardvarks, Ducks.

Likewise for the second sheet; the rows could be in any order.

Any suggestions?

Hi,
How come the items on sheet 2 can be in a different order? Aren't they
derived from sheet 1? If not, are they entered manually?
Regards - Dave.

On Jun 16, 12:31 pm, jiml wrote:
Suppose I have data that looks like:

1/1/2008 1/3/2008 2/7/2008
Ordered Ordered Ordered
Aardvarks 8 7 3
Baboons 3 10 7
Cats 5 3 2
Ducks 5 1 7
Elephants 7 2 8

I want to get, by month, the total of animals on another sheet

January February
Ordered Ordered
Ducks 6 7
Aardvarks 15 3
Elephants 9 8
Baboons 13 7
Cats 8 2

The tricky part is that I can't control what rows will go where. The two
lists sorting order will change, and new items might be added at any time
(say the zoo wants to add Flamingoes and Giraffes)

That is, for the first sheet, you might see Aardvarks, Baboons, Cats, Ducks,
Elephants, but you could also see Baboons, Elephants, Cats, Aardvarks, Ducks.

Likewise for the second sheet; the rows could be in any order.

Any suggestions?

Well, if there is only ever one row of each animal, a simple VLOOKUP
will do the trick. If there is more than one row, probably a SUMIF or
certainly a SUMPRODUCT would work. Or, if it is more than one row and
this is a massive number of formulas that you will need, eventually
DSUM would be faster than the SUMxx formulas.

On Jun 17, 2:25 pm, Spiky wrote:
On Jun 16, 12:31 pm, jiml wrote:



Suppose I have data that looks like:

1/1/2008 1/3/2008 2/7/2008
Ordered Ordered Ordered
Aardvarks 8 7 3
Baboons 3 10 7
Cats 5 3 2
Ducks 5 1 7
Elephants 7 2 8

I want to get, by month, the total of animals on another sheet

January February
Ordered Ordered
Ducks 6 7
Aardvarks 15 3
Elephants 9 8
Baboons 13 7
Cats 8 2

The tricky part is that I can't control what rows will go where. The two
lists sorting order will change, and new items might be added at any time
(say the zoo wants to add Flamingoes and Giraffes)

That is, for the first sheet, you might see Aardvarks, Baboons, Cats, Ducks,
Elephants, but you could also see Baboons, Elephants, Cats, Aardvarks, Ducks.

Likewise for the second sheet; the rows could be in any order.

Any suggestions?

Well, if there is only ever one row of each animal, a simple VLOOKUP
will do the trick. If there is more than one row, probably a SUMIF or
certainly a SUMPRODUCT would work. Or, if it is more than one row and
this is a massive number of formulas that you will need, eventually
DSUM would be faster than the SUMxx formulas.

Sorry, forgot...
If the main issue is the possible length of the data, just make sure
your database area is large enough. If you currently have 20 lines,
make the data area in the formulas 50 lines, just to be sure.