Hi

I have a number of columns with a lot of rows:

A U V W


Green 1 0 0
Yellow 1 0 0
Green 1 1 0
Orange 0 1 1
Red 0 1 0
Green 0 0 1
Yellow 1 0 1

I want to be able to produce the following table

U V W
Green 3 1 1
Yellow 2 0 1
Orange 0 1 1
Red 0 1 0

So basically a summary. Basically do a count on the number of Greens
in column A if U has a corresponding 1 and so on for every colour and
each letter? Probably not explaned well IF U is a 1 count the colour
next to it.

Can anyone help pleasE?

Cheers

Rich

Suppose your lower (summary) table begins at row 1000 and your main
data occupies rows 2 to 800. Put this formula in cell U1000:

=SUMIF($A$2:$A$800,$A1000,U$2:U$800)

Copy the formula into V1000 and W1000. Then highlight U1000:W1000 and
copy/paste into the cells below for as many rows as you have in your
summary table.

Hope this helps.

Pete

On Jul 26, 2:02 pm, Richhall wrote:
Hi

I have a number of columns with a lot of rows:

A U V W

Green 1 0 0
Yellow 1 0 0
Green 1 1 0
Orange 0 1 1
Red 0 1 0
Green 0 0 1
Yellow 1 0 1

I want to be able to produce the following table

U V W
Green 3 1 1
Yellow 2 0 1
Orange 0 1 1
Red 0 1 0

So basically a summary. Basically do a count on the number of Greens
in column A if U has a corresponding 1 and so on for every colour and
each letter? Probably not explaned well IF U is a 1 count the colour
next to it.

Can anyone help pleasE?

Cheers

Rich

I had my results in A10:D14 and raw data in so in A2:D8 to

B11: =SUMPRODUCT(($A$2:$A$8=$A11)*($B$1:$D$1=B$10)*$B$2 :$D$8)

copy across and down

for your matrix

=SUMPRODUCT(($A$2:$A$8=$A11)*($U$1:$W$1=U$10)*$U$2 :$W$8)


Change ranges (sheets) as required.

A B C D

10 U V W <== your range
11 Green 2 1 1
12 Yellow 2 0 1
13 Orange 0 1 1
14 Red 0 1 0


"Richhall" wrote:

Hi

I have a number of columns with a lot of rows:

A U V W


Green 1 0 0
Yellow 1 0 0
Green 1 1 0
Orange 0 1 1
Red 0 1 0
Green 0 0 1
Yellow 1 0 1

I want to be able to produce the following table

U V W
Green 3 1 1
Yellow 2 0 1
Orange 0 1 1
Red 0 1 0

So basically a summary. Basically do a count on the number of Greens
in column A if U has a corresponding 1 and so on for every colour and
each letter? Probably not explaned well IF U is a 1 count the colour
next to it.

Can anyone help pleasE?

Cheers

Rich