Formula Help Needed

  • Thread starter Thread starter Rudy Beuc
  • Start date Start date
R

Rudy Beuc

I'm creating a volunteer list for a nonprofit group I belong to.

I've been experminting with counta, dcountf, countif, ect...., but no luck
yet.

On the first worksheet I have all of the volunteer's names, phone number's,
ect...
Columns are the catagories and the rows are the volunteers.

There is a series of four columns representing shifts. The column
immediately following the shift columns indicates which work area of the
event they have volunteered for. This column is validated and has a drop
down list of predetermined choices.

Currently I'm trying to create a table listing the individual work areas and
how many voulnteers they have for each shift.

Here is my problem. I'm hitting a wall trying to come up with a formula
expression that will return a sum of nonblank cells within a shift column
based upon what has been selected in the work area column for each
individual row.

Any help would be appreciated,
Thanks,

Rudy Beuc
 
Yes, I I tried using the explanitions in the help files but it all drove me
batty.

I'll give it a shot later with the help from the link you gave.

Oh, and also, this workbook might be used on the chairman's PDA. I don't
know if pivot tables wil function within excel CE.

Thanks,
Rudy
 
Rudy,

If I understand your situation correctly, I can think of a solution using
some helper columns.

I'm envisioning your shift/area assignments as a table that might look like
this:
Shift1 Shift2 Shift3 Shift4
name name name <blank> Area1
<blank> name name <blank> Area2
<blank> <blank> name <blank> Area1
For illustrative purposes, I'll assume the title "Shift1" is in cell A1

If so, create columns 4 more columns to the right, and repeat your Shift1
through 4 column headings. Now, in the upper left corner of your new table
(cell F2 for me) type the following formula:
=IF(ISBLANK(A2),0,1)
and copy it to all cells in the table.

Now, I assume the table you're looking for has the static list of areas and
looks like this:
Vol/Shift Shift1 Shift2 Shift3 Shift4
Area1 1 1 2 0
Area2 0 1 1 0
Area3 0 0 0 0
I started this table with the Vol/Shift label in cell A6 for my test.
Therefore, in cell B7, I typed the following formula:
=SUMIF($E$2:$E$4,$A7,F$2:F$4)
where A7 points to the "Area1" title and E2:E4 points to your fifth column,
the one with the validated values of work areas, and F2:F4 points to all
your 1's and 0's for each row. By placing the $'s in the formula as I have
indicated, you can actually just copy the formula for the whole table, and
you're done.

Now, I'm assuming you don't really know how many rows you're going to have
in your work assignment table. Therefore, I recommed that you fill your
formulas for the 1's and 0's down a couple hundred rows (or some amount that
you'll never exceed). Also, you can put your "Volunteers per Shift" table
on a different tab and then switch your references from E2:E4 and f2:F4 to
E:E and F:F to just make counts based on every row in the whole sheet.
 
Rudy,

One thing to note: not all formulas are supported by Pocket Excel.
However, when a spreadsheet is copied to the PDA, the formulas taht are not
supported are converted to values and the spreadsheet can still be read,
thought he functionality is lost if you want to be able to change values
which were used in the formulas that were no longer there. In addition, you
can not copy the spreadsheet from the PDA back tot he computer, because the
formulas will stay lost.

If my straightforward IF(ISBLANK() and SUMIF solution works for you, you
should be all set - I ahven't checked it, but those shuold be supported. If
not, there ARE non-Microsoft versions of Excel out there which actually
support more features and functions than Microsoft's version. If you need a
reference to one, post back

Trevor
 
Thanks for the reply.

Actually my first sheet looks more like this. The intention is to have a
foolproof way for other people to share the spreadsheet and enter data.


Name Address (Snip) Shift 1 Shift 2 Shift 3 Shift 4
Area

John Doe 123xyz X X
Check-In Booth
Jane Doe 456pdq X X
Route Marshal
Jim Dean 417 Blvd X
Check-In Booth
Me XXXX X X X
X Miscelanious


I have the Shift columns validated too so anyone entering in information can
only place an x or leave the space blank. Of course I've experimented and
validation is gone when transfered to the PDA. Hopefully the chairman can
cope with this.


Thanks,
Rudy
 
Wow, that did not display right.

Lets try agian

Name Address Shift 1 Shift 2 Shift 3 Area

John Doe 123xyz X X Check-In
Jane Doe 456pdq X X Route
Jim Dean 417 Blvd X X Check-In
Me XXXX X X X Miscelanious


(I eleminated Shift 4 to prevent word wrap.)

Thanks
Rudy
 
Yes, that's what I thought. Adding 4 columns to the right with
=IF(B3="x",1,0) will do the trick. Then you can create the table with the
SUMIF function I mentioned.

Alternatively, switch from X's to 1's and use the SUMIF function directly on
the assignment table you depict below. It's best on a separate tab, so you
can just use =SUMIF($F:$F,$A2,C:C) where A2 is the title of the first area,
and you least each area down column A.

As I said before, you can send me the spreadsheet and I can put it in for
you, if you like.
 
Back
Top