Write a total number of hours each month into appropriate cell

[koli99]

Hi!

I'm new to this forum and I'm looking for some help. I'm not a programmer, but I'm trying to make an excel document for following workers activities.


I have a base of workers which log each day their activities in hours (D01-D11). I would like a macro to sum total hours of each project (project numbers are from 320-516) and put it into the table on the left.

For example: For PERSON 1, I want that the cell I60 writes 10, and cell I74 = 6. For worker 2 (PERSON 2) would be AK60 = 3,5; AK67 = 8 and AK74 = 8.

In total where would have been 10 workers.

Is it possible to program this?

Thanks for help!

Regards,

Jure


BAZA.xlsx

[Bob Phillips]

You can use a formula

=SUMPRODUCT(($AC$5:$AC$216=$C20)*(MONTH($Q$5:$Q$216)=COLUMN(A1))*($R$5:$AB$ 216))

but you will need to adjust it for each person because of your layout

[koli99]

Thanks for reply.

What would A1 represent in your formula?

PS: I've tried copy/pase your code into cell I20 (month June), removed dollar sign for C20 (as the project is changing) and extened it down. All the values are 0? What am I doing wrong?

[Bob Phillips]

The A1 is just to allow testing of the month number of the dates, Column(A1) is 1, and month 1 is January, and it will increment in each column copied across to pick up each month.

You should have put the formula I gave you in D20, and copied down to Dn and across to On. You must not remove the dollar sign from $C20, that is referring to the product column and will remain static as you copy across. Copying down will increment the row number so it will get the next item that way.

[koli99]

Great!

Thanks for help! This is exactly what I have been looking for.

Cudos to you.

Best Regards,

Jure