Suggest you put in as a fresh, new posting for this, Linda. In your new
posting, you could paste the actual formulas that you're using. It'll be
easier for responders to diagnose what's wrong.
Generally, ensure that ranges used are identically sized and sufficiently
cover the max expected source ranges.
And if you're receiving zero returns where it obviously shouldn't be, look
out for possible data consistency issues, eg: extra "invisible" white
spaces -- as mentioned earlier in this thread -- where the liberal use of
TRIM around the source ranges and the lookup values in the sumproduct would
then help improve robustness of matching and correct returns.
Example
In B2:
=IF(OR(B$1="",$A2=""),"",SUMPRODUCT((Sheet1!$A$1:$ A$100=$A2)*(Sheet1!$B$1:$B$100=B$1)))
You could try instead in B2, with TRIM:
=IF(OR(TRIM(B$1)="",TRIM($A2)=""),"",SUMPRODUCT((T RIM(Sheet1!$A$1:$A$100)=TRIM($A2))*(TRIM(Sheet1!$B $1:$B$100)=TRIM(B$1))))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---