Excel count based on value in another column

[Richhall]

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

 

[Pete_UK]

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

 

[Guest]

I had my results in A1014 and raw data in so in A28 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