Excel count based on value in another column
 

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

Excel count based on value in another column
 

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

Excel count based on value in another column
 

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