Sumproduct problem

[burl_h]

I'm having a difficult time with the following sumproduct calculation.

The main problem is that I want to sum all cells in column S, that do
not have a value present in the corresponding row in column I. Column
I is sparely populated with some values, the majority of column I has
empty cells. But it's very important that I only calculate column S
where no value is present in column I.

Column D contains names that match cell Y34, column K and N are start
and end dates.

=IF($I$2:$I$4139<>"",0,SUMPRODUCT(--($D$2:$D$4139=Y34)*($S$2:$S
$4139),--($K$2:$K$4139>=$Y$6),--($N$2:$N$4139<=$Y$7)))

Any help would be greatly appreciated.

Thanks
burl_rfc

 

[Mike H]

Try this

=SUMPRODUCT((I2:I4139="")*(D24139=Y34)*(K2:K4139>=Y6)*(N2:N4139<=Y7)*(S2:S4139))

Mike

 

[burl_h]

Mike H,

Thanks it worked a treat.

But why is the "*" working in this case, I've always used "," to
separate the statements?

Thanks

burl_h

 

[Mike H]

Hi,

It's just an alternative syntax for sumproduct, both have their uses.

the website seems to be having problems at the moment but have a look here
for help on sumproduct

http://www.xldynamic.com/

Mike

 

[Shane Devenshire]

Hi,

The other syntax will work just as well and there are times when, with a
slight modification, it would prevent a problem that the * syntax would
generate. So this is equivalent:

=SUMPRODUCT(I$2:I$4139="",D$2$4139=Y34,K$2:K$4139>=Y$6,N$2:N$4139<=Y$7,S$2:S$4139)

as is this, which is slightly safer in some cases:

=SUMPRODUCT(--(I$2:I$4139=""),--(D$2$4139=Y34),--(K$2:K$4139>=Y$6),--(N$2:N$4139<=Y$7),S$2:S$4139)


Minor points, in both syntaxes you can drop the final ()'s as shown above.
If you are copying downward you only need absolutes ($) in front of the row
numbers.