Forumula Help?

  • Thread starter Thread starter Morpheseus
  • Start date Start date
M

Morpheseus

I can't figure out the formula for this problem.

Column A contains a "1" or is blank. (the 1 means the guy worked that day)
Column B contains numbers 1 - 26 (These are my work gang numbers)
The other columns are names and Id numbers and various other stuff.

What I want to do is have a formula that will count the number of persons
working if I do a sort by gang. For example, I use the autofilter and
say... sort by gang 22. I might have fifty persons in gang 22, but only the
one that work that day get a "1" in Column A. I have another sheet with all
kinds of formulas counting my total workers... etc.... and total numbers
assigned to gangs etc... (countif... formulas)

But I need to have a formula that will count the number if column A contains
a "1". Alternatively, I could make the formulas on the reporting sheet
list each gang seperately....
if gang="1" and column A contains 1 then count
if gang= "2" and column A contains 1 then count
if gang= "3" and column A contains 1 then count
if gang= "4" and column A contains 1 then count

and total for each gang.

Does this make any sense?
I have looked everywhere and read all I can. I've been to Oz and can't find
what I need.

Can someone suggest a formula that work?

Thanks in advance.
Nick.
 
If you filter on gang in column B and on 1 in A wouldn't that show the
number that worked that day,
then to count the visible cell use =SUBTOTAL(3,A2:A500)
 
I tried that. It counted everthing in column A. There is a "1" if they
worked, there is an "n" if they were a no show, or an "s" if they were sick
etc..... I need to count the ones that worked.
It is closer to what I need.. but I have to find a way to count the 1's.

Nick.
 
Let A1:B500 be the unfiltered range that houses your data, where A1:B1
contains labels.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A500,ROW(A2:A500)-MIN(ROW(A2:A500)),,1))*(A
2:A500=1))

would give you the desired conditional count after filtering for some
"gang".
 
Try this option:

Assume you have in Sheet1, the table below in A1:C3

Status...Gang......S_G
1…….....1……...1_1
…….......2……...0_2
1…….....3……...1_3

where
Col A = Status col, with either
"1" = the guy worked
blank = otherwise
etc

col B = Gang #s
col C = S_G (a new join field, Status_Gang)

Put in C2: =IF(ISBLANK(A2),0&"_"&TRIM(B2),TRIM(A2&"_"&B2))
copy down col C

In your reporting sheet (another sheet),
assume you have your gang numbers 1-26
in col A, A2 downwards, viz:

Gang....1
1..........1
2..........0
3..........1
etc

Put in B1: 1 (this "1" is to capture the guys who worked)

Put in B2: =COUNTIF(Sheet1!C:C,TRIM(B$1&"_"&A2))
copy down col B

This will return the count of the guys in each gang # who worked in Sheet1

In the same manner, if desired, you can create in cols C, D etc
to capture the count of the other Status* for each gang #
*other than "1"

hth
Max
 
Have you considered using a Pivot Table. It generally eats this kind of stuff
for breakfast. From the sound of your data I can't see any reason why it
wouldn't do all you need and a hell of a lot more that you didn't realise you
could do - and a lot simpler as well. If you want to try, then Debra Dalgleish
has a great intro here:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm
 
=sumproduct(--(A2:A100=1),--(B2:B100=26))

Will count the workers in Gang 26

Assume you put in the numbers 1 to 26 in Sheet2 in A1 to A26

In B1 put in the formula

=SumProduct(--(Sheet1!$A$2:$A$200=1),--(Sheet1!$B$2:$B$200=$A1))

Then drag fill down to B26
 
Tom,
That was perfect. I am doing a spreadsheet for a jail I work in and we have
nearly 400 inmates working on 26 various gangs. I have a sheet called
"Reporting" that gives the managers all the critical data. We have limits
to the number of inmates to a gang and so your formula tells our front line
supervisors when we've reached the max count for that gang. We then know
not to assign any more. I am having to manage the numbers with excel
because senior manager fear that if I use Access, there is to much risk
exposure in the event that I go work somewhere else. Access seems to be
require more "expertise" and a ton more VBA skill than most employees have.

This formual work perfectly for us. Plus I will be able to adapt it to
report other things in the sheet too.... like numbers who attend Drug and
Alcohol programming or Violence Prevention programs etc.....

Much appreciated Tom,

Thanks.

Nick Walsh
 
Your welcome.

All the people who answered you could and would have given you that formula,
but I believe you confused/led them astry by talking about the Autofilter.
Anyway, glad it worked. By the way, you can add conditions to that formula
using the same technique - add commas and put in conditions.
 
Again you are bang on. I had just sat down at the computer to write a
clarification. I was going to describe it differently. I ready your post
and pasted the formula into my sheet. It worked great. I then went out to
the local bookstore and purchased "Excel Programming - Weekend Crash Course"
by Peter G. Aitken. I need to get a better understanding of this stuff. I
am not a programmer, but have 25,000 hours experience on computers. I'm
just good enough to get myself in trouble in a wide range of programs!

I've never been able to find a "jump on" spot for learning VBA or Visual
Basic. I don't really want to go back and learn basic and C.

Thanks...
Nick.
 
Back
Top