Whats wrong with this expression (VALUE).

**Jo-Jo** · 12-11-2009, 01:02 AM

Sorry NBVC,

The forum locked up whilst I was sending and I didn't see your last message until it came back to life. I will try what you mentioned.

**Jo-Jo** · 12-11-2009, 01:04 AM

Sorry NBVC,

The forum locked up whilst I was sending and I didn't see your last message until it came back to life. I will try what you mentioned.

**NBVC** · 12-11-2009, 01:11 AM

I checked against your sheet... these give results:

=IF(H18="","",SUM(IF($C$18:$E$397=H18,IF($D$18:$F$397<>"",1))))

and

=IF(H18="","",SUM(IF($C$18:$E$397=H18,IF($D$18:$F$397>=5,1))))

confirmed with CSE keys and copied down.

**Jo-Jo** · 12-11-2009, 01:36 AM

NBVC, yes that works, but I'm getting value errors in cols K & M (simple math functions) if cell E11 is less than 20 (4-20 range).

I've chased this from the original sumproduct that I corrected, then it moved to these two that you have corrected, I thought that because they are no formula in these that the last problem would also correct those.

**NBVC** · 12-11-2009, 01:57 AM

How about, doing the test for column H being blank or not..

e.g in K18

=IF(H18="","",I18-J18)

and in M18:

=IF(H18="","",I18*9-L18)

copied down

**Jo-Jo** · 12-11-2009, 02:10 AM

I'll give that a go later today. It's 07:00am here and I've been at this for most of the last 18hrs and I'm just not thinking straight anymore

.

I'll let you know how I get on.

Thanks yet again for your help NBVC (I'll look forward to the car repair section

)

G'night/morn everyone.

**Jo-Jo** · 12-11-2009, 08:27 AM

Hi NBVC, all,

Tried it and that worked too. Now I seem to have moved a problem to the last col, under certain conditions.

I'm going to have another go today, if I can't get it this time, I'll try an alternative like putting it in dummy (do you call this 'helper') columns first or just doing away with part of it.

Thanks everyone, much appreciated all the help again.
J.

**NBVC** · 12-11-2009, 08:58 AM

If you can tell us what conditions cause problems, we may be able to diagnose it better.

**Jo-Jo** · 12-13-2009, 06:40 AM

Originally Posted by NBVC

If you can tell us what conditions cause problems, we may be able to diagnose it better.

Hi NBVC,

Apologies for the delay in replying. I needed to turn my attention to repairing a mixing desk and snake that got damaged and was needed urgently by my band this w/end.

I got everything else working nicely with the s/shhet. The only thing I found was the last Col in the example... if there are more entries in Cols D & F than required by cell E11, then Col O ( =SUMIF($C$18:$E$397;$H18;$D$18:$F$397) )displays residual data instead of blanking it out.

This isn't going to be a problem, because (unless I forget to clear it) there shouldn't be any extra entries left in Cols D & F. I'm going to sit down this afternoon and check with different scenarios, but I think it's just a tidyness issue and affects nothing.

Thanks again for all of your help.
J.