Count Text Across Multiple Columns

[LaTanya]

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.

 

[Mike H]

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

 

[T. Valko]

Try these:

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

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

 

[LaTanya]

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]

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

?????

 

[LaTanya]

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]

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!

 

[LaTanya]

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]

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