# Sumproduct problem

B

#### 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

Try this

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

Mike

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

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

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.