Sumproduct Formula not working

[srinivasan0610]

Hi all. I have scanned various threads pertaining to Sumproduct formula and it has been helpful. However I am stuck with a particular one. My data is in subsheet 1 and I need the sumproduct formula in the 2nd subsheet. There are only 2 columns to be evaluated against each other. The first one contains team names and the 2nd one contains target dates. I tried using the syntax; =sumproduct('sheet1A1:A7000,"=ship"),('sheet1B1:B7000,A1). The last A1 is the date that I want to use as the comparison base and will be different per row of subsheet 2. My final objective is to find how many items for a particular team have a target date as per A1, A2 etc in subsheet 2. Please help. The team names and target dates in sheet 1 are formulated using vlookup and simple workday formulae respectively, Many thanks

 

[Kaps_mr2]

ok try each column in turn and work out which column is causing the error :-

SUMPRODUCT(--('001'!$AL$2:$AL$7458="Ship"))

SUMPRODUCT(--('001'!$AM$2:$AM$7458=A10)).

Thanks

Kaps

 

[DonkeyOte]

The #N/A error implies underlying NA() errors in source data - check both ranges (and criteria cell(s))

If you need to leave the errors in place you should revert to an Array.

To find which column contains the errors run =COUNTIF(< range ref >,"#N/A")

 

[srinivasan0610]

Thanks a bunch to all. It worked now. I eliminated the source of error. As basic as that.