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="")*(D2:D4139=Y34)*(K2:K4139 =Y6)*(N2:N4139<=Y7)*(S2:S4139))

Mike

"burl_h" wrote:

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,

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

"burl_h" wrote:

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,

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:D$4139=Y34,K$2:K$413 9=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:D$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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"burl_h" wrote:

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