I am trying to count text and number in in column

example-I need a function that will count across two columns
If the training hours =8 and Taining Type =CT3 or CT4
Results Would be =1

If the training hours =10 and Taining Type =CT3 or CT4
Results Would be =2

Column-1 Column-2
Training Hours Training Type
8 CT3
10 CT4
8 LR
8 QC
8 RSV
10 CT3
10 QC



Thanks
LaTanya R.

Hi,

Try this in column C and drag down

=IF(AND(A2=8,OR(B2="CT3",B2="CT4")),1,IF(AND(A2=10 ,OR(B2="CT3",B2="CT4")),2,"Unspecified"))

There are a lot of things you don't specify.

Mike

"LaTanya" wrote:

I am trying to count text and number in in column

example-I need a function that will count across two columns
If the training hours =8 and Taining Type =CT3 or CT4
Results Would be =1

If the training hours =10 and Taining Type =CT3 or CT4
Results Would be =2

Column-1 Column-2
Training Hours Training Type
8 CT3
10 CT4
8 LR
8 QC
8 RSV
10 CT3
10 QC



Thanks
LaTanya R.

Try these:

=SUMPRODUCT((A2:A8=8)*(B2:B8={"CT3","CT4"}))

=SUMPRODUCT((A2:A8=10)*(B2:B8={"CT3","CT4"}))

--
Biff
Microsoft Excel MVP


"LaTanya" wrote in message
...
I am trying to count text and number in in column

example-I need a function that will count across two columns
If the training hours =8 and Taining Type =CT3 or CT4
Results Would be =1

If the training hours =10 and Taining Type =CT3 or CT4
Results Would be =2

Column-1 Column-2
Training Hours Training Type
8 CT3
10 CT4
8 LR
8 QC
8 RSV
10 CT3
10 QC



Thanks
LaTanya R.

Now I have a new problem

example-I need a function that will count across two columns
If Column B =Meeting or Vac and it's less than 8 or 10 hrs, I need a formula
that would count the discriptor and add the hours

if b12:b15=MT or VAC or TRN and c12:b15 is less than 8hr or 10hr= sum of the
hours

Thanks Again for your help

LaTanya R.

"T. Valko" wrote:

Try these:

=SUMPRODUCT((A2:A8=8)*(B2:B8={"CT3","CT4"}))

=SUMPRODUCT((A2:A8=10)*(B2:B8={"CT3","CT4"}))

--
Biff
Microsoft Excel MVP


"LaTanya" wrote in message
...
I am trying to count text and number in in column

example-I need a function that will count across two columns
If the training hours =8 and Taining Type =CT3 or CT4
Results Would be =1

If the training hours =10 and Taining Type =CT3 or CT4
Results Would be =2

Column-1 Column-2
Training Hours Training Type
8 CT3
10 CT4
8 LR
8 QC
8 RSV
10 CT3
10 QC



Thanks
LaTanya R.

Are these 2 separate conditions:

If Column B =Meeting or Vac and it's less than 8 or 10 hrs

if b12:b15=MT or VAC or TRN and c12:b15 is less than 8hr or 10hr

What about this:

less than 8hr or 10hr

8 hrs is less than 10 hrs

?????

--
Biff
Microsoft Excel MVP


"LaTanya" wrote in message
...
Now I have a new problem

example-I need a function that will count across two columns
If Column B =Meeting or Vac and it's less than 8 or 10 hrs, I need a
formula
that would count the discriptor and add the hours

if b12:b15=MT or VAC or TRN and c12:b15 is less than 8hr or 10hr= sum of
the
hours

Thanks Again for your help

LaTanya R.

"T. Valko" wrote:

Try these:

=SUMPRODUCT((A2:A8=8)*(B2:B8={"CT3","CT4"}))

=SUMPRODUCT((A2:A8=10)*(B2:B8={"CT3","CT4"}))

--
Biff
Microsoft Excel MVP


"LaTanya" wrote in message
...
I am trying to count text and number in in column

example-I need a function that will count across two columns
If the training hours =8 and Taining Type =CT3 or CT4
Results Would be =1

If the training hours =10 and Taining Type =CT3 or CT4
Results Would be =2

Column-1 Column-2
Training Hours Training Type
8 CT3
10 CT4
8 LR
8 QC
8 RSV
10 CT3
10 QC



Thanks
LaTanya R.

8 and 10 represent hours worked by techs, I am trying to get the total hours
of training,meeting and vacations hours that are less than 8 or 10 hrs

for example
if T258 has vacation for 3 hours a day for 3 days I need the results to
equal 9total hours

it would be the same for Training and meetings


"T. Valko" wrote:

Are these 2 separate conditions:

If Column B =Meeting or Vac and it's less than 8 or 10 hrs

if b12:b15=MT or VAC or TRN and c12:b15 is less than 8hr or 10hr

What about this:

less than 8hr or 10hr

8 hrs is less than 10 hrs

?????

--
Biff
Microsoft Excel MVP


"LaTanya" wrote in message
...
Now I have a new problem

example-I need a function that will count across two columns
If Column B =Meeting or Vac and it's less than 8 or 10 hrs, I need a
formula
that would count the discriptor and add the hours

if b12:b15=MT or VAC or TRN and c12:b15 is less than 8hr or 10hr= sum of
the
hours

Thanks Again for your help

LaTanya R.

"T. Valko" wrote:

Try these:

=SUMPRODUCT((A2:A8=8)*(B2:B8={"CT3","CT4"}))

=SUMPRODUCT((A2:A8=10)*(B2:B8={"CT3","CT4"}))

--
Biff
Microsoft Excel MVP


"LaTanya" wrote in message
...
I am trying to count text and number in in column

example-I need a function that will count across two columns
If the training hours =8 and Taining Type =CT3 or CT4
Results Would be =1

If the training hours =10 and Taining Type =CT3 or CT4
Results Would be =2

Column-1 Column-2
Training Hours Training Type
8 CT3
10 CT4
8 LR
8 QC
8 RSV
10 CT3
10 QC



Thanks
LaTanya R.

Ok, but the less than 8 or 10 hrs is going to be a problem.

You want to sum up the time if it is less than 8 *or* 10 hrs.

VAC...8
VAC...10

8 hrs is less than 10 hrs so by your logic then 8 hrs should be included in
the sum.

Or, maybe I'm just not understanding what you want. I'm kind of "thick"
sometimes!

--
Biff
Microsoft Excel MVP


"LaTanya" wrote in message
...
8 and 10 represent hours worked by techs, I am trying to get the total
hours
of training,meeting and vacations hours that are less than 8 or 10 hrs

for example
if T258 has vacation for 3 hours a day for 3 days I need the results to
equal 9total hours

it would be the same for Training and meetings


"T. Valko" wrote:

Are these 2 separate conditions:

If Column B =Meeting or Vac and it's less than 8 or 10 hrs

if b12:b15=MT or VAC or TRN and c12:b15 is less than 8hr or 10hr

What about this:

less than 8hr or 10hr

8 hrs is less than 10 hrs

?????

--
Biff
Microsoft Excel MVP


"LaTanya" wrote in message
...
Now I have a new problem

example-I need a function that will count across two columns
If Column B =Meeting or Vac and it's less than 8 or 10 hrs, I need a
formula
that would count the discriptor and add the hours

if b12:b15=MT or VAC or TRN and c12:b15 is less than 8hr or 10hr= sum
of
the
hours

Thanks Again for your help

LaTanya R.

"T. Valko" wrote:

Try these:

=SUMPRODUCT((A2:A8=8)*(B2:B8={"CT3","CT4"}))

=SUMPRODUCT((A2:A8=10)*(B2:B8={"CT3","CT4"}))

--
Biff
Microsoft Excel MVP


"LaTanya" wrote in message
...
I am trying to count text and number in in column

example-I need a function that will count across two columns
If the training hours =8 and Taining Type =CT3 or CT4
Results Would be =1

If the training hours =10 and Taining Type =CT3 or CT4
Results Would be =2

Column-1 Column-2
Training Hours Training Type
8 CT3
10 CT4
8 LR
8 QC
8 RSV
10 CT3
10 QC



Thanks
LaTanya R.

Yes that would be ideal, but if that won't work how about

If b12:b16=vac,loa,ml,sc,trn (count)*c12:16=1-10
Add total hrs for each discriptor

Example:T125 has vacation 10hrs, vac 4 hrs, vaca 2hrs=16 total hours


"T. Valko" wrote:

Ok, but the less than 8 or 10 hrs is going to be a problem.

You want to sum up the time if it is less than 8 *or* 10 hrs.

VAC...8
VAC...10

8 hrs is less than 10 hrs so by your logic then 8 hrs should be included in
the sum.

Or, maybe I'm just not understanding what you want. I'm kind of "thick"
sometimes!

--
Biff
Microsoft Excel MVP


"LaTanya" wrote in message
...
8 and 10 represent hours worked by techs, I am trying to get the total
hours
of training,meeting and vacations hours that are less than 8 or 10 hrs

for example
if T258 has vacation for 3 hours a day for 3 days I need the results to
equal 9total hours

it would be the same for Training and meetings


"T. Valko" wrote:

Are these 2 separate conditions:

If Column B =Meeting or Vac and it's less than 8 or 10 hrs

if b12:b15=MT or VAC or TRN and c12:b15 is less than 8hr or 10hr

What about this:

less than 8hr or 10hr

8 hrs is less than 10 hrs

?????

--
Biff
Microsoft Excel MVP


"LaTanya" wrote in message
...
Now I have a new problem

example-I need a function that will count across two columns
If Column B =Meeting or Vac and it's less than 8 or 10 hrs, I need a
formula
that would count the discriptor and add the hours

if b12:b15=MT or VAC or TRN and c12:b15 is less than 8hr or 10hr= sum
of
the
hours

Thanks Again for your help

LaTanya R.

"T. Valko" wrote:

Try these:

=SUMPRODUCT((A2:A8=8)*(B2:B8={"CT3","CT4"}))

=SUMPRODUCT((A2:A8=10)*(B2:B8={"CT3","CT4"}))

--
Biff
Microsoft Excel MVP


"LaTanya" wrote in message
...
I am trying to count text and number in in column

example-I need a function that will count across two columns
If the training hours =8 and Taining Type =CT3 or CT4
Results Would be =1

If the training hours =10 and Taining Type =CT3 or CT4
Results Would be =2

Column-1 Column-2
Training Hours Training Type
8 CT3
10 CT4
8 LR
8 QC
8 RSV
10 CT3
10 QC



Thanks
LaTanya R.

Well, I'm totally confused so I'm bowing out on this one. Maybe a fresh set
of eyes will see what you want.

--
Biff
Microsoft Excel MVP


"LaTanya" wrote in message
...
Yes that would be ideal, but if that won't work how about

If b12:b16=vac,loa,ml,sc,trn (count)*c12:16=1-10
Add total hrs for each discriptor

Example:T125 has vacation 10hrs, vac 4 hrs, vaca 2hrs=16 total hours


"T. Valko" wrote:

Ok, but the less than 8 or 10 hrs is going to be a problem.

You want to sum up the time if it is less than 8 *or* 10 hrs.

VAC...8
VAC...10

8 hrs is less than 10 hrs so by your logic then 8 hrs should be included
in
the sum.

Or, maybe I'm just not understanding what you want. I'm kind of "thick"
sometimes!

--
Biff
Microsoft Excel MVP


"LaTanya" wrote in message
...
8 and 10 represent hours worked by techs, I am trying to get the total
hours
of training,meeting and vacations hours that are less than 8 or 10 hrs

for example
if T258 has vacation for 3 hours a day for 3 days I need the results to
equal 9total hours

it would be the same for Training and meetings


"T. Valko" wrote:

Are these 2 separate conditions:

If Column B =Meeting or Vac and it's less than 8 or 10 hrs

if b12:b15=MT or VAC or TRN and c12:b15 is less than 8hr or 10hr

What about this:

less than 8hr or 10hr

8 hrs is less than 10 hrs

?????

--
Biff
Microsoft Excel MVP


"LaTanya" wrote in message
...
Now I have a new problem

example-I need a function that will count across two columns
If Column B =Meeting or Vac and it's less than 8 or 10 hrs, I need a
formula
that would count the discriptor and add the hours

if b12:b15=MT or VAC or TRN and c12:b15 is less than 8hr or 10hr=
sum
of
the
hours

Thanks Again for your help

LaTanya R.

"T. Valko" wrote:

Try these:

=SUMPRODUCT((A2:A8=8)*(B2:B8={"CT3","CT4"}))

=SUMPRODUCT((A2:A8=10)*(B2:B8={"CT3","CT4"}))

--
Biff
Microsoft Excel MVP


"LaTanya" wrote in message
...
I am trying to count text and number in in column

example-I need a function that will count across two columns
If the training hours =8 and Taining Type =CT3 or CT4
Results Would be =1

If the training hours =10 and Taining Type =CT3 or CT4
Results Would be =2

Column-1 Column-2
Training Hours Training Type
8 CT3
10 CT4
8 LR
8 QC
8 RSV
10 CT3
10 QC



Thanks
LaTanya R.