My main data table looks as so:

Firm MarketShare
ABC 20
ABC 10
ABC 2

I would like to construct a new table that looks like this:

0 10 20

ABC 0 2 1
EFG
HIJ
KLM

The formula in b3 would find a match to A3 in table above, then count the
number of times that the marketshare is equal to 0. Then in c3, I would like
the formula to count the number of times the market share is greater than 0
(a2) and less than or equal to 10 (a3).

Thank you in advance.

On Tue, 3 May 2005 12:55:03 -0700, "carl"
wrote:

My main data table looks as so:

Firm MarketShare
ABC 20
ABC 10
ABC 2

I would like to construct a new table that looks like this:

0 10 20

ABC 0 2 1
EFG
HIJ
KLM

The formula in b3 would find a match to A3 in table above, then count the
number of times that the marketshare is equal to 0. Then in c3, I would like
the formula to count the number of times the market share is greater than 0
(a2) and less than or equal to 10 (a3).


I think you should use sumproduct instead of Countif:
Try these:
In B3:= SUMPRODUCT(($A14:$A16=$A3)*($B14:$B16=B1))
In C3:=SUMPRODUCT(($A14:$A16=$A3)*($B14:$B16<=C1))-B3
Where your main table is in A14:B16.

In B3 it counts all cases where Firm equals A3 and MarketShare equals
B1.
C3 does the same except all cases where MarketShare is less or equal,
and then subtract the value in B3.

Hope this makes sense.

Per Erik

small point

In B3:= SUMPRODUCT(($A14:$A16=$A3)*($B14:$B16=B$1))
In C3:=SUMPRODUCT(($A14:$A16=$A3)*($B14:$B16<=C$1))-B3

copy C3 across, and B3:x3 down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Per Erik Midtrød" wrote in message
...
On Tue, 3 May 2005 12:55:03 -0700, "carl"
wrote:

My main data table looks as so:

Firm MarketShare
ABC 20
ABC 10
ABC 2

I would like to construct a new table that looks like this:

0 10 20

ABC 0 2 1
EFG
HIJ
KLM

The formula in b3 would find a match to A3 in table above, then count the
number of times that the marketshare is equal to 0. Then in c3, I would
like
the formula to count the number of times the market share is greater than
0
(a2) and less than or equal to 10 (a3).


I think you should use sumproduct instead of Countif:
Try these:
In B3:= SUMPRODUCT(($A14:$A16=$A3)*($B14:$B16=B1))
In C3:=SUMPRODUCT(($A14:$A16=$A3)*($B14:$B16<=C1))-B3
Where your main table is in A14:B16.

In B3 it counts all cases where Firm equals A3 and MarketShare equals
B1.
C3 does the same except all cases where MarketShare is less or equal,
and then subtract the value in B3.

Hope this makes sense.

Per Erik