Dear ALL,
I am a Project Manager, Surveyor involved in new ships building. I have to control and report the budget and actual hours every month and at the end of the project. I need to calculate the sum of the actually spent hours for the inspections done every month and send them to our head office. I use SUMPRODUCT function but Excel displays #VALUE! error eventhough there are no cells used in the formula which contain text!
The formula in E11 = SUMPRODUCT(--(MONTH(E2:E8)=MONTH(E10)),--(YEAR(E2:E8)=YEAR(E10)),H2:H8)
E2:E8 and E10 are formated as a date. I tested all of them with ISNUMBER and the result is TRUE!!!
Could you advise please where is the mistake???Is there other formula I can use for the intended purpose?
![]()
E F G H
1 DATE SURVEYOR BUDGET HOURS ACTUAL HOURS
2 20/11/2008 KL 2 1.5
3 3/10/2008 SSN 4 3.25
4 25/11/2008 JJO 1 1.5
5 9/12/2008 BMC 4 5.25
6 1/1/2009 KL 0.5 1
7 15/10/2008 KL 1 1
8 01/12/2009 JJK 2.5 1
9 Total Hours - 15.0 14.5
10 Oct-08 Nov-08 Dec-08 Jan-09
11 #VALUE! #VALUE! #VALUE! #VALUE!
Bookmarks