G
Guest
Hi All
I have a spreadsheet with various formula on it taking data from an access
database, with help from here i managed to get the whole thing working except
one column, this column works fine for a bit, then for no apparent reason it
goes back to zero and starts again.
Ill explain:
Im using this formula on one sheet to calculate the total time taken to
repair equipment in a given month on sheet 'with costs'
=SUMPRODUCT(--(TEXT('With costs'!P24991,"mm yyyy")="10 2007"),--('With
costs'!H2:H4991),--('With costs'!E2:E4991="Faulty"))
Column 'P' on the 'with costs' sheet is the date
Column 'H' on the 'with costs' sheet is the time
Column 'E' on the 'with costs' sheet is the text 'Faulty' or other text
All columns are formatted correctly and it works for a while then goes back
to zero, i cant explain why
Alternatively if this doesnt work, i think i can calculate the total time
worked in the month by using the total cost column (as i know the hourly
rate) but i dont know how to do this either lol
e.g. cell 'L12' = £333.11 (total cost per month) cell 'L11' has the total
time worked
How would i format the cells
Thanks very much for any help you have as im completely lost
Derek
I have a spreadsheet with various formula on it taking data from an access
database, with help from here i managed to get the whole thing working except
one column, this column works fine for a bit, then for no apparent reason it
goes back to zero and starts again.
Ill explain:
Im using this formula on one sheet to calculate the total time taken to
repair equipment in a given month on sheet 'with costs'
=SUMPRODUCT(--(TEXT('With costs'!P24991,"mm yyyy")="10 2007"),--('With
costs'!H2:H4991),--('With costs'!E2:E4991="Faulty"))
Column 'P' on the 'with costs' sheet is the date
Column 'H' on the 'with costs' sheet is the time
Column 'E' on the 'with costs' sheet is the text 'Faulty' or other text
All columns are formatted correctly and it works for a while then goes back
to zero, i cant explain why
Alternatively if this doesnt work, i think i can calculate the total time
worked in the month by using the total cost column (as i know the hourly
rate) but i dont know how to do this either lol
e.g. cell 'L12' = £333.11 (total cost per month) cell 'L11' has the total
time worked
How would i format the cells
Thanks very much for any help you have as im completely lost
Derek