Thanks, Jerry !
Cheers
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On Feb 6, 10:52 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
Max, BIG THANKS!! Wouldn't have done it without a UDF if you hadn't pointed
the way to the OFFSET() use. I just didn't think of trying that and had
given up barring help from someone. Definitely giving you the "Answered the
Question" award on this one.

The following formula is what I came up with based on the reality, having
conned Jane into sending me an actual sheet out of the real-world workbook.
=IF(N9=0,"",IF(ISERROR(IF(P9="","",IF(P9="NC",$N9/SUM(OFFSET(M9,ROW(M$8)-RO*W(M9),,,-(COUNTIF(B9:M9,"0")-1))),N9/$N$8))),"",IF(P9="","",IF(P9="NC",$N9*/SUM(OFFSET(M9,ROW(M$8)-ROW(M9),,,-(COUNTIF(B9:M9,"0")-1))),N9/$N$8))))

First IF takes care of rows that have an NC entry (or other) but the sum
total of sales is zero, the second IF takes care of potential #DIV/0 errors
and the rest does the work.