Sumproduct cell reference vs embedded "values"
 

I have experimented with all sorts of different combinations and there is
clearly some syntax issue that I am running afowl of or something I do not
understand about the way this works.

In trying to compare and count the outcome using two column ranges, looking
for one name any number of times in the first column and only including that
in the count if the corresponding column position is a blank cell, this
formula works:

=SUMPRODUCT((F2:F200=W2)*(Q2:Q200=AC10))

....where cell W2 is "7566" and cell AC10 is blank.

But when I try to embed the cell contents directly into the formula i.e.

=SUMPRODUCT((F2:F200="7566")*(Q2:Q200=""))

....it does not work.

Insight please?! Thanks

Sumproduct cell reference vs embedded "values"
 

Remove quotes around the number 7566


"BobMcBarker" wrote:

I have experimented with all sorts of different combinations and there is
clearly some syntax issue that I am running afowl of or something I do not
understand about the way this works.

In trying to compare and count the outcome using two column ranges, looking
for one name any number of times in the first column and only including that
in the count if the corresponding column position is a blank cell, this
formula works:

=SUMPRODUCT((F2:F200=W2)*(Q2:Q200=AC10))

...where cell W2 is "7566" and cell AC10 is blank.

But when I try to embed the cell contents directly into the formula i.e.

=SUMPRODUCT((F2:F200="7566")*(Q2:Q200=""))

...it does not work.

Insight please?! Thanks

Sumproduct cell reference vs embedded "values"
 

Many thanks...easy, but I completely spaced it!

"Teethless mama" wrote:

Remove quotes around the number 7566


"BobMcBarker" wrote:

I have experimented with all sorts of different combinations and there is
clearly some syntax issue that I am running afowl of or something I do not
understand about the way this works.

In trying to compare and count the outcome using two column ranges, looking
for one name any number of times in the first column and only including that
in the count if the corresponding column position is a blank cell, this
formula works:

=SUMPRODUCT((F2:F200=W2)*(Q2:Q200=AC10))

...where cell W2 is "7566" and cell AC10 is blank.

But when I try to embed the cell contents directly into the formula i.e.

=SUMPRODUCT((F2:F200="7566")*(Q2:Q200=""))

...it does not work.

Insight please?! Thanks

Sumproduct cell reference vs embedded "values"
 

You're Welcome!

"BobMcBarker" wrote:

Many thanks...easy, but I completely spaced it!

"Teethless mama" wrote:

Remove quotes around the number 7566


"BobMcBarker" wrote:

I have experimented with all sorts of different combinations and there is
clearly some syntax issue that I am running afowl of or something I do not
understand about the way this works.

In trying to compare and count the outcome using two column ranges, looking
for one name any number of times in the first column and only including that
in the count if the corresponding column position is a blank cell, this
formula works:

=SUMPRODUCT((F2:F200=W2)*(Q2:Q200=AC10))

...where cell W2 is "7566" and cell AC10 is blank.

But when I try to embed the cell contents directly into the formula i.e.

=SUMPRODUCT((F2:F200="7566")*(Q2:Q200=""))

...it does not work.

Insight please?! Thanks