A SUM(IF in VB????

[Wray69]

Hello All,

Not sure where to start with this one. What I have is a List of managers in Column A, B has an employees name, C if they are full time or part time, D thru AH each repersents a day of the month (1-31) with either a 1.0 or a .5 in each row under them repersenting if they where full or part time.

Looks something like this;

November 2003 01 02 03

Manager Name F/P
French Baker, Kolanni Full 1.0 1.0 1.0
Heft Bouvia, Andrew Full 1.0 1.0 1.0
Sibley Cousino, William Full 1.0 1.0 1.0

What I would like to do is be able to press a button and have it give me the average for each manager, I know this is VB but not sure where to start with it. Can someone point me in the right directoin?

Thanks,

Wray

 

[ETID]

No need for vba..


This is a job for the pivot table.

 

[Wray69]

Thanks, I thought about a pivot table but I dont want it on a different tab. What I would like is just to have a form pop with the info I need on it....

Cheers,

Wray

 

[PeterMoran]

Hi Wray69,

I'm not sure what you mean by the average for each manager, but you might like to consider this.

Below your list of employees add a row for each manager with his/her name in Col A. Enter the following formula in col D against the first manager:

=IF(SUMIF($A$3:$A$10,$A13,D$3$10)=0,"",SUMIF($A$3:$A$10,$A13,D$3$10))

where the employees rows are 3 - 10 and the first manager total line is row 13. This formula can be copied down to all the managers in col D and then across for each day of the month.

Then at either the right end of the first manager's totals (or possibly more conveniently in the "F/P" column) enter the following formula:

=AVERAGE(D13:AI13)

and then copy down to the other managers.

This will give you an average, but is it what you want?

Note: The IF and SUMIF formula gives you a null when a zero occurs which AVERAGE ignores. A simple SUMIF formula will give a zero when there are no entries and it will be included in the AVERAGE.

Hope this helps.
Good Luck!
Peter Moran

 

[KenWright]

Just for info, a pivot table can be on the same tab, though it is wise to have it to the right and below your existing data if you go that route.

Regards
Ken.........

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[KenWright]

Though in hindsight your data is in tabular format and not database format, so not ideal for a Pivot Table. That can be gotten round, but why wouldn't you just add another column after Full/Part Time and use =AVERAGE(E4:AI4). You could even add an autofilter to the data so that you could select a manager name from a list.

If however you are saying that this will be a list of managesr with more than one employee, and it is the average for that manager you are looking for, have you considered sorting the data by Manager, and then using Data / Subtotals and using Average for your criteria.

Regards
Ken...............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------