Couting the number of referrences that...

[speakers_86]

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.

[Gary]

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

Change the range as per your need.

let me know if this is what u wanted.


"speakers_86" <[email protected]>
wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> speakers_86
> ------------------------------------------------------------------------
> speakers_86's Profile:
> http://www.excelforum.com/member.php...o&userid=36839
> View this thread: http://www.excelforum.com/showthread...hreadid=565463
>

[speakers_86]

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.

[Gary]

one way is

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


"speakers_86" <[email protected]>
wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> speakers_86
> ------------------------------------------------------------------------
> speakers_86's Profile:
> http://www.excelforum.com/member.php...o&userid=36839
> View this thread: http://www.excelforum.com/showthread...hreadid=565463
>

[speakers_86]

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

[Biff]

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

"speakers_86" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>
>
> --
> speakers_86
> ------------------------------------------------------------------------
> speakers_86's Profile:
> http://www.excelforum.com/member.php...o&userid=36839
> View this thread: http://www.excelforum.com/showthread...hreadid=565463
>

[speakers_86]

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.

[Biff]

"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

[speakers_86]

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

thanks biff!

[Biff]

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

"speakers_86" <[email protected]>
wrote in message
news:[email protected]...
>
> The sheet is called master copy
> range J:J criteria Water
> range L:L criteria no job
>
> thanks biff!
>
>
> --
> speakers_86
> ------------------------------------------------------------------------
> speakers_86's Profile:
> http://www.excelforum.com/member.php...o&userid=36839
> View this thread: http://www.excelforum.com/showthread...hreadid=565463
>

[Biff]

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

"speakers_86" <[email protected]>
wrote in message
news:[email protected]...
>
> The sheet is called master copy
> range J:J criteria Water
> range L:L criteria no job
>
> thanks biff!
>
>
> --
> speakers_86
> ------------------------------------------------------------------------
> speakers_86's Profile:
> http://www.excelforum.com/member.php...o&userid=36839
> View this thread: http://www.excelforum.com/showthread...hreadid=565463
>

[speakers_86]

you rock. thanks biff

[Biff]

You're welcome!

Biff

"speakers_86" <[email protected]>
wrote in message
news:[email protected]...
>
> you rock. thanks biff
>
>
> --
> speakers_86
> ------------------------------------------------------------------------
> speakers_86's Profile:
> http://www.excelforum.com/member.php...o&userid=36839
> View this thread: http://www.excelforum.com/showthread...hreadid=565463
>