Couting the number of referrences that...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

speakers_86 - 27 Jul 2006 05:30 GMT

What formula can i use that can count the number of rows that have
specified text?

for example:
A B C
Stevens Water Job Job secured

if this were one record of many, i need a formula to count the number
of records that say water job and job secured.

Signature

speakers_86

Reply to this Message

Gary - 27 Jul 2006 05:44 GMT

=COUNTIF(A:A,"Water Job")

Change the range as per your need.

let me know if this is what u wanted.

> What formula can i use that can count the number of rows that have
> specified text?
[quoted text clipped - 5 lines]
> if this were one record of many, i need a formula to count the number
> of records that say water job and job secured.

Reply to this Message

speakers_86 - 27 Jul 2006 05:55 GMT

im not in the office now, but that looks like it will work. How do I
add another criteria? i need to know how many records say water AND
secured

i appreciate your help.

Signature

speakers_86

Reply to this Message

Gary - 27 Jul 2006 06:09 GMT

one way is

=countif(A:A,"water")+countif(a:a,"secured")

> im not in the office now, but that looks like it will work. How do I
> add another criteria? i need to know how many records say water AND
> secured
>
> i appreciate your help.

Reply to this Message

speakers_86 - 27 Jul 2006 06:22 GMT

actually thats not exactly what i need. if a job comes in, it is
classified as mold, water, fire damage, etc. Then, there is the status
of the job. Wether it was secured, no job, pending. im looking for a
formula that will look for water jobs in one column, then out of those
jobs, count the number that are no job. thnx

Signature

speakers_86

Reply to this Message

Biff - 27 Jul 2006 06:56 GMT

Hi!

Try this:

=SUMPRODUCT(--(B1:B10="water job"),--(C1:C10="no job"))

Better to use cells to hold the criteria:

D1 = water job
E1 = no job

=SUMPRODUCT(--(B1:B10=D1),--(C1:C10=E1))

Biff

> actually thats not exactly what i need. if a job comes in, it is
> classified as mold, water, fire damage, etc. Then, there is the status
> of the job. Wether it was secured, no job, pending. im looking for a
> formula that will look for water jobs in one column, then out of those
> jobs, count the number that are no job. thnx

Reply to this Message

speakers_86 - 27 Jul 2006 20:18 GMT

None of those formulas seem to work. I need a =countif that reffers to
a different sheet and looks at two ranges and two different criterias.

Signature

speakers_86

Reply to this Message

Biff - 27 Jul 2006 21:12 GMT

"speakers_86" wrote...

> None of those formulas seem to work. I need a =countif that reffers to
> a different sheet and looks at two ranges and two different criterias.

Ok...

Tell us EXACTLY which sheet and EXACTLY which two ranges and EXACTLY what
the two criteria are.

Biff

Reply to this Message

speakers_86 - 28 Jul 2006 02:49 GMT

The sheet is called master copy
range J:J criteria Water
range L:L criteria no job

thanks biff

Reply to this Message

Biff - 28 Jul 2006 03:11 GMT

Try this:

=SUMPRODUCT(--('Master Copy'!J1:J65535="Water"),--('Master
Copy'!L1:L65535="no job"))

You can't use entire columns as ranges with Sumproduct J:J, L:L

Biff

> The sheet is called master copy
> range J:J criteria Water
> range L:L criteria no job
>
> thanks biff!

Reply to this Message

speakers_86 - 28 Jul 2006 03:43 GMT

you rock. thanks biff

Signature

speakers_86

Reply to this Message

Biff - 28 Jul 2006 03:52 GMT

You're welcome!

Biff

> you rock. thanks biff

Reply to this Message