1. ## sumproduct formula (excel2003)

I have this huge data in columns A:J, and I want to extract for all cups(cup 7 in this case)
the first date from column D when WCUT(column J) is not blank. I used the sumproduct function in column N3,
which gives me a strange date value. the correct date is cell highlited in green.

2. ## Re: sumproduct formula (excel2003)

Your formula sums all dates that satisfy the conditions instead of returning the first one. I'll think about a formula that does what you want.

3. ## Re: sumproduct formula (excel2003)

Try the following array formula (confirm with Ctrl+Shift+Enter):

=INDEX(\$D\$2:\$D\$10,MATCH(1,(\$A\$2:\$A\$10=L3)*(\$B\$2:\$B \$10=M3)*(\$J\$2:\$J\$10>0),0))

Explanation: (\$A\$2:\$A\$10=L3)*(\$B\$2:\$B\$10=M3)*(\$J\$2:\$J\$10>0) results in an array of 0s and 1s. The MATCH function returns the position of the first 1, and INDEX returns the value in that position in D210.

4. ## Re: sumproduct formula (excel2003)

But to explain what you did: The date you have is not really "strange" it is the sum of all the dates that meet the criteria given (which is D9 + D10). You have nothing to indicate that you only want to sum 1 cell.

Steve

5. ## Re: sumproduct formula (excel2003)

Perfect,
Thanks. HansV

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•