COUNT

[TC]

Hi, I am using Microsoft 2003. I am trying to add the amount of X's in my
excel and seperate them by the YEAR/WK.

For example I want to know how many X's I have for 200948. For this
function I dont care about the column they are in just how many X's there
are. My sheet is much larger than the one below but it is a sample. I
understand I will have to have a new function per pln_s_yrwk. Any help
would be great!!



yrwk SOC OPC PUR M. PLN
D. PLN
200948 0 0 0 x 0
200951 0 x 0 0 0
200948 0 0 0 x x
200947 0 0 x 0 0
200946 0 0 0 0 x

 

[Dave Peterson]

=SUMPRODUCT((A1:A5=200948)*(B1:F5="x"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

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

 

[Mike H]

Hi,

This assumes your data are in columns A through E

=SUMPRODUCT((A1:A10=200948)*(B1:E10="x"))

Mike

 

[Joe User]

Hi, I am using Microsoft 2003. I am trying to add the amount of X's
in my excel and seperate them by the YEAR/WK.
For example I want to know how many X's I have for 200948. For
this function I dont care about the column they are in just how many
X's there are.

Kudos for providing a concise example. But it would have been helpful if
you told us Xs you would count for 200948: 3 (all Xs), or just 2 (number of
rows with one or more Xs).

As clear as you might think your description is, you'd be surprised how many
times people describe their requirements clearly incorrectly ;-).

If you would count 3 Xs for 200948, then:

=SUMPRODUCT((B2:F5="x")*(A2:A5=200948))

If you would count 2 Xs for 200948, then:

=SUMPRODUCT(--(COUNTIF(INDIRECT("B"&ROW(2:6)&":F"&ROW(2:6)),"x")>0) *
(A2:A6=200948))


----- original message -----