Counting Occurrences with 2 columns

[excelmedia]

Hello!

I am trying to calculate the occurence of one item based on tw
criterias:

Column A: Name of Magazine(e.g. "Red", "Blue", "Green" )
Column B: Frequnecy read (4,5,8)range from 1-20
Column C: "Read" OR "Not Read"


e.g

Red 4 Read
Blue 5 Not Read
Green 6 Read
Yellow 2 Read
Red 3 Not Read

What I need to find out is:

How many many times each magazine was read ?


Thank yo

 

[Max]

Suppose your data is in Sheet1 in A2:C6

Red 4 Read
Blue 5 Not Read
Green 6 Read
Yellow 2 Read
Red 3 Not Read

Try this set-up in a new sheet, say Sheet2
-------------
List down in A2:A5 the names: Red, Blue, Green, Yellow
Put across in B2:C2 the phrases: Read, Not Read

Put in B2:
=SUMPRODUCT((Sheet1!$A$2:$A$6=$A2)*(Sheet1!$C$2:$C$6=B$1)*(Sheet1!$B$2:$B$6)
)

Copy B2 across to C2, then copy down to C5

[Note that the ranges used in SUMPRODUCT have to be similar
and you can't use entire columns e.g.: A:A, B:B, C:C]

The above will give you the desired counts

And for a cleaner look, you can suppress the zeros from showing in Sheet2:

Select Sheet2
Click Tools > Options > View tab > Uncheck "Zero values" > OK

------------
Another way is to use a Pivot table (PT)
(which is quite ideal for this sort of purpose)

Some example steps to set-up a PT are given
in a previous reply to a similar query:
http://tinyurl.com/3h5vw

 

[Max]

Suppose your data is in Sheet1 in A2:C6

Red 4 Read
Blue 5 Not Read
Green 6 Read
Yellow 2 Read
Red 3 Not Read

Try this set-up in a new sheet, say Sheet2
-------------
List down in A2:A5 the names: Red, Blue, Green, Yellow
Put across in B2:C2 the phrases: Read, Not Read

Put in B2:
=SUMPRODUCT((Sheet1!$A$2:$A$6=$A2)*(Sheet1!$C$2:$C$6=B$1)*(Sheet1!$B$2:$B$6)
)

Copy B2 across to C2, then copy down to C5

[Note that the ranges used in SUMPRODUCT have to be similar
and you can't use entire columns e.g.: A:A, B:B, C:C]

The above will give you the desired counts

And for a cleaner look, you can suppress the zeros from showing in Sheet2:

Select Sheet2
Click Tools > Options > View tab > Uncheck "Zero values" > OK

------------
Another way is to use a Pivot table (PT)
(which is quite ideal for this sort of purpose)

Some example steps to set-up a PT are given
in a previous reply to a similar query:
http://tinyurl.com/3h5vw