Thread: Couting the number of referrences that...

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.

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

Change the range as per your need.

let me know if this is what u wanted.


"speakers_86" <speakers_86.2bl6wa_1153974613.0591@excelforum-nospam.com>
wrote in message
news:speakers_86.2bl6wa_1153974613.0591@excelforum-nospam.com...
>
> 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
>

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.

one way is

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


"speakers_86" <speakers_86.2bl81n_1153976103.0316@excelforum-nospam.com>
wrote in message
news:speakers_86.2bl81n_1153976103.0316@excelforum-nospam.com...
>
> 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
>

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

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" <speakers_86.2bl9fn_1153977903.4851@excelforum-nospam.com>
wrote in message
news:speakers_86.2bl9fn_1153977903.4851@excelforum-nospam.com...
>
> 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
>

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.

"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

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

thanks 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" <speakers_86.2bmu5k_1154051711.8332@excelforum-nospam.com>
wrote in message
news:speakers_86.2bmu5k_1154051711.8332@excelforum-nospam.com...
>
> 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
>

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" <speakers_86.2bmu5k_1154051711.8332@excelforum-nospam.com>
wrote in message
news:speakers_86.2bmu5k_1154051711.8332@excelforum-nospam.com...
>
> 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
>

you rock. thanks biff

You're welcome!

Biff

"speakers_86" <speakers_86.2bmwp7_1154054716.0229@excelforum-nospam.com>
wrote in message
news:speakers_86.2bmwp7_1154054716.0229@excelforum-nospam.com...
>
> 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
>