I have a counting question.

I have some columns that have codes and numbers in them and want to count
the amount of times that they appear in that specific column. That is easy.
But I also have other data in other columns that I want the count function to
refer to. So I have one column with codes in (e.g. ABC and DEF) and I have
another column with weights in (e.g 0.7 and 1.4). I want to create a
function that counts how many times ABC appears when it weighs less than 1.0.
Can I do this?

Also, can I expand the function further. For example I have another column
with length in it (e.g 120 and 340). Can I have a function that counts the
amount of times that ABC appears when it weighs less than 1.0 and is longer
that 200?

This can be accomplished easily with an autofilter, look it up and use custom
to put in <1 or whatever. There is also a count of records matching the
criteria at the bottom left of your screen
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"carl" wrote:

I have a counting question.

I have some columns that have codes and numbers in them and want to count
the amount of times that they appear in that specific column. That is easy.
But I also have other data in other columns that I want the count function to
refer to. So I have one column with codes in (e.g. ABC and DEF) and I have
another column with weights in (e.g 0.7 and 1.4). I want to create a
function that counts how many times ABC appears when it weighs less than 1.0.
Can I do this?

Also, can I expand the function further. For example I have another column
with length in it (e.g 120 and 340). Can I have a function that counts the
amount of times that ABC appears when it weighs less than 1.0 and is longer
that 200?

Hi Carl

Try
=SUMPRODUCT(($A$1:$A$100="ABC")*($B$1:$B$100<1)*($ C$1:$C$100200))
Change ranges to suit, but ensure that they are of equal length.
--
Regards

Roger Govier


"carl" wrote in message
...
I have a counting question.

I have some columns that have codes and numbers in them and want to
count
the amount of times that they appear in that specific column. That is
easy.
But I also have other data in other columns that I want the count
function to
refer to. So I have one column with codes in (e.g. ABC and DEF) and I
have
another column with weights in (e.g 0.7 and 1.4). I want to create a
function that counts how many times ABC appears when it weighs less
than 1.0.
Can I do this?

Also, can I expand the function further. For example I have another
column
with length in it (e.g 120 and 340). Can I have a function that
counts the
amount of times that ABC appears when it weighs less than 1.0 and is
longer
that 200?

Carl

I want to create a function
that counts how many times ABC appears when it weighs less than 1.0.

Try
=sumproduct((a1:a10="ABC")*(b1:b10<1))

Can I have a function that counts the amount of times
that ABC appears when it weighs less than 1.0 and is longer
that 200?

=sumproduct((a1:a10="ABC")*(b1:b10<1)*(c1:c10<200) )

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"carl" wrote:

I have a counting question.

I have some columns that have codes and numbers in them and want to count
the amount of times that they appear in that specific column. That is easy.
But I also have other data in other columns that I want the count function to
refer to. So I have one column with codes in (e.g. ABC and DEF) and I have
another column with weights in (e.g 0.7 and 1.4). I want to create a
function that counts how many times ABC appears when it weighs less than 1.0.
Can I do this?

Also, can I expand the function further. For example I have another column
with length in it (e.g 120 and 340). Can I have a function that counts the
amount of times that ABC appears when it weighs less than 1.0 and is longer
that 200?