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
 
M

Mike H

Try this

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

Mike
 
B

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
 
S

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:D$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: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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top