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

 

[prabby25101981]

The formula should be -

=SUMPRODUCT(--(sheet1A1:A7000="ship"),--(sheet1B1:B7000=A1))

 

[MrKowz]

Welcome to the forums!

You need to add -- before each of those conditions. Basically, putting -- before a condition will return a 1 or a 0. 1 Being a true result, 0 being a false result.

=SUMPRODUCT(--('Sheet1'!$A$1:$A$7000="ship"),--('Sheet1'!$B$1:$B$7000=A1))

 

[srinivasan0610]

Thanks. Will try and revert with the findings.

 

[srinivasan0610]

Hi,

Nope it does not work. What am I doing wrong? Here is the formula that I used - =SUMPRODUCT(--('001'!$AL$2:$AL$7458="Ship"),--('001'!$AM$2:$AM$7458=A10)).

Please help. Need this thing to be working to complete the project. Thanks.

p.s - The version that I am using is the 2003 SP3 (11.8326.8324). Does it matter?

 

[Aladin Akyurek]

Hi,

Nope it does not work. What am I doing wrong? Here is the formula that I used - =SUMPRODUCT(--('001'!$AL$2:$AL$7458="Ship"),--('001'!$AM$2:$AM$7458=A10)).

Please help. Need this thing to be working to complete the project. Thanks.

p.s - The version that I am using is the 2003 SP3 (11.8326.8324). Does it matter?

The version is not an issue. The formula is syntactically correct. Does the formula return a wrong result or an error value?

Does the following do better:

=SUMPRODUCT(--(TRIM('001'!$AL$2:$AL$7458)="Ship"),--('001'!$AM$2:$AM$7458=A10)

 

[srinivasan0610]

Still no luck! Just to reiterate, the source data in cell range AK and AL in sheet '001' are formulated to pull the team name (e.g Ship) and date respectively. Can this factor be the issue?

 

[snowblizz]

Still no luck! Just to reiterate, the source data in cell range AK and AL in sheet '001' are formulated to pull the team name (e.g Ship) and date respectively. Can this factor be the issue?

You say AK & AL but the formula you posted has AL & AM?
Also what is the formula returning? The wrong answer, an error?

 

[Kaps_mr2]

Just an observation in the opening post we have A1 in the second argument, and now we have A10 :-
SUMPRODUCT(--('001'!$AL$2:$AL$7458="Ship"),--('001'!$AM$2:$AM$7458=A10)).

SUMPRODUCT(--(sheet1A1:A7000="ship"),--(sheet1B1:B7000=A1))

Thanks

Kaps

 

[srinivasan0610]

The formula is returning an error #N/A. The cell references do not matter as those were just examples. I am using the right range when testing in my sheet. Thanks again.