Hi,

I want to be able to count the number of times a code, say UO, occurs in a
column, but only for rows which have certain text in another column.

So for example, the number of time UO appears in column A, when Creative
Technology is in Column B in the example below:

A B

UO Sound & Music for Interactive Games
O Information Management
UF Information Studies
UO Creative Technology
UF Information Systems
UF Games Design
UO Creative Technology
UF Creative Technology

Any suggestions would be appreciated!

I bet you get a flurry of answers :-)

=SUMPRODUCT(--(A1:A8="UO"),--(B1:B8="Creative Technology))

or

=SUMPRODUCT((A1:A8="UO")*(B1:B8="Creative Technology))

--
Allllen


"AdamINN" wrote:

Hi,

I want to be able to count the number of times a code, say UO, occurs in a
column, but only for rows which have certain text in another column.

So for example, the number of time UO appears in column A, when Creative
Technology is in Column B in the example below:

A B

UO Sound & Music for Interactive Games
O Information Management
UF Information Studies
UO Creative Technology
UF Information Systems
UF Games Design
UO Creative Technology
UF Creative Technology

Any suggestions would be appreciated!

Maybe you could use column C to tell you if column A contains the text you
want AND if column B contains the other text. Then if C is equal to, for
instance, "TRUETRUE", it means it's a match. Then you can use
"=count.if(C:C;"TRUETRUE")"



"AdamINN" wrote...
Hi,

I want to be able to count the number of times a code, say UO, occurs in a
column, but only for rows which have certain text in another column.

So for example, the number of time UO appears in column A, when Creative
Technology is in Column B in the example below:

A B

UO Sound & Music for Interactive Games
O Information Management
UF Information Studies
UO Creative Technology
UF Information Systems
UF Games Design
UO Creative Technology
UF Creative Technology

Any suggestions would be appreciated!

=sumproduct(--(a1:a100="UO"),--(b1:b100="Creative Technology"))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

AdamINN wrote:

Hi,

I want to be able to count the number of times a code, say UO, occurs in a
column, but only for rows which have certain text in another column.

So for example, the number of time UO appears in column A, when Creative
Technology is in Column B in the example below:

A B

UO Sound & Music for Interactive Games
O Information Management
UF Information Studies
UO Creative Technology
UF Information Systems
UF Games Design
UO Creative Technology
UF Creative Technology

Any suggestions would be appreciated!

--

Dave Peterson