Count Text Across Multiple Columns

  • Thread starter Thread starter LaTanya
  • Start date Start date
L

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.
 
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
 
Try these:

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

=SUMPRODUCT((A2:A8=10)*(B2:B8={"CT3","CT4"}))
 
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.
 
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
 
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!
 
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
 
Well, I'm totally confused so I'm bowing out on this one. Maybe a fresh set
of eyes will see what you want.
 
Back
Top