#value

[Rob #4]

Can someone help me? I have a spreadsheet which all the functions work, but if I click in a cell and then click out it turns the formula to say #value, even though the formula is correct. Is there a way to recalculate or something?

 

[VoG]

You can press F9.


What is the formula?

 

[Rob #4]

=SUM(IF(FREQUENCY(IF('Resident Worker Hour Report'!$A$19:$A$400=$D143,IF('Resident Worker Hour Report'!$B$19:$B$400=$B143,IF('Resident Worker Hour Report'!$K$4:$DY$4=E$139,IF('Resident Worker Hour Report'!$K$19:$DY$400<>"",MATCH('Resident Worker Hour Report'!$D$19:$D$400&'Resident Worker Hour Report'!$C$19:$C$400,'Resident Worker Hour Report'!$D$19:$D$400&'Resident Worker Hour Report'!$C$19:$C$400,0))))),ROW('Resident Worker Hour Report'!$A$19:$A$400)-ROW('Resident Worker Hour Report'!$A$19)+1),1))

Just pressing F9 is not working for me though

 

[VoG]

There must be error values in the ranges that you are trying to operate upon,

 

[Aladin Akyurek]

=SUM(IF(FREQUENCY(IF('Resident Worker Hour Report'!$A$19:$A$400=$D143,IF('Resident Worker Hour Report'!$B$19:$B$400=$B143,IF('Resident Worker Hour Report'!$K$4:$DY$4=E$139,IF('Resident Worker Hour Report'!$K$19:$DY$400<>"",MATCH('Resident Worker Hour Report'!$D$19:$D$400&'Resident Worker Hour Report'!$C$19:$C$400,'Resident Worker Hour Report'!$D$19:$D$400&'Resident Worker Hour Report'!$C$19:$C$400,0))))),ROW('Resident Worker Hour Report'!$A$19:$A$400)-ROW('Resident Worker Hour Report'!$A$19)+1),1))

Just pressing F9 is not working for me though

You need to confirm that formula with control+shift+enter, not just with enter.