count

[Darrell_Sarrasin]

I am using a countif to cout how many times something is appearing in a
column. I need a code that does a dual column count. example: if column c
says tom then go to column g and count if it says red.

Any help is greatly appreciated.

 

[T. Valko]

Try this:

=SUMPRODUCT(--(C1:C10="Tom"),--(G1:G10="Red"))

Better to use cells to hold the criteria:

A1 = Tom
A2 = Red

=SUMPRODUCT(--(C1:C10=A1),--(G1:G10=A2))

Note that you can't use entire column references unless you're using Excel
2007.

Biff

 

[Guest]

Try this
=COUNTIF(C1:C10,"=tom")+COUNTIF(G1:G10,"=red")

 

[Darrell_Sarrasin]

Sorry I may have mis said this. I want it so that if something happens in
column C then it goes to column G and counts it. Right now it is counting
everytime that the result from column g shows up.

example in c it says tom. I want it to look and on the same row only look
for red and count it. Right now it is counting everytime red shows up in
that column

 

[Susan]

only look

for red and count it.

you mean the color red, or the word red?

susan

 

[Darrell_Sarrasin]

just using it as an example. The actual sheet that we are working on is a
termination of employment sheet. I need it to read so that if the company
the person is working for shows up then go across and count the reason why
they left.

only look
for red and count it.

you mean the color red, or the word red?

susan

Sorry I may have mis said this. I want it so that if something happens in
column C then it goes to column G and counts it. Right now it is counting

[quoted text clipped - 10 lines]

 

[Susan]

ok, how 'bout this?

you have your text in column c.

in column g, insert an if formula, like:
=if($g1="red","1"," ")OR(if($g1="Tom","2"," ")
(which i can't get to work at the moment, something must be wrong with the
syntax......
but you get the idea)
then you'll end up with
column column
c g
red 1
yellow
jim
tom 2
red 1
blue
red 1

at the bottom you can sort them out:

=countif(range("1"))
and =countif(range("2"))
(in separate columns).
hope this helps!
susan

just using it as an example. The actual sheet that we are working on is a
termination of employment sheet. I need it to read so that if the company
the person is working for shows up then go across and count the reason why
they left.

only look
for red and count it.

you mean the color red, or the word red?

susan

Sorry I may have mis said this. I want it so that if something happens
in
column C then it goes to column G and counts it. Right now it is
counting

[quoted text clipped - 10 lines]

Any help is greatly appreciated.

 

[T. Valko]

ok, how 'bout this?

Or, you could try my suggestion!

Biff

ok, how 'bout this?

you have your text in column c.

in column g, insert an if formula, like:
=if($g1="red","1"," ")OR(if($g1="Tom","2"," ")
(which i can't get to work at the moment, something must be wrong with the
syntax......
but you get the idea)
then you'll end up with
column column
c g
red 1
yellow
jim
tom 2
red 1
blue
red 1

at the bottom you can sort them out:

=countif(range("1"))
and =countif(range("2"))
(in separate columns).
hope this helps!
susan

just using it as an example. The actual sheet that we are working on is
a
termination of employment sheet. I need it to read so that if the
company
the person is working for shows up then go across and count the reason
why
they left.

only look
for red and count it.

you mean the color red, or the word red?

susan

Sorry I may have mis said this. I want it so that if something happens
in
column C then it goes to column G and counts it. Right now it is
counting
[quoted text clipped - 10 lines]

Any help is greatly appreciated.

 

[Darrell_Sarrasin]

I cant say that I have every used the feature you mentioned. it will work
with text and the listing in the column?

ok, how 'bout this?

Or, you could try my suggestion!

Biff

ok, how 'bout this?

[quoted text clipped - 46 lines]

 

[Darrell_Sarrasin]

This worked great thank you !!!!. can you by any chance email me to let me
know how it works. I have never used this feature before. sarrasin@vianet.
ca

 

[T. Valko]

You're welcome.

See this for a detailed explanation:

http://xldynamic.com/source/xld.SUMPRODUCT.html

Biff

 

[Susan]

biff's suggestions was definitely simpler!
susan