# Thread: problem with excel formula (2003)

1. ## problem with excel formula (2003)

In attached file - Trying to fill in cell D15….I CANT GET IT RIGHT FOR SOME REASON - I NEED TO SEE 58 IN THAT BOX!!
Here's what ive got so far...
=SUM((Data!\$G\$2:\$G\$5001>=\$A15)*(Data!\$G\$2:\$G\$5001< =\$B15)*(Data!\$J\$2:\$J\$5001=\$D\$2))

Thanks

2. ## Re: problem with excel formula (2003)

You should either confirm the formula with Ctrl+Shift+Enter to make it an array formula, or use

=SUMPRODUCT((Data!\$G\$2:\$G\$5001>=\$A15)*(Data!\$G\$2:\$ G\$5001<=\$B15)*(Data!\$J\$2:\$J\$5001=\$D\$2))

which is an implicit array formula. But there is a problem: in calculations, empty cells equal 0, so the formula also counts all empty rows. So you must add a test to exclude empty rows:

=SUMPRODUCT((Data!\$G\$2:\$G\$5001>=\$A15)*(Data!\$G\$2:\$ G\$5001<=\$B15)*(Data!\$J\$2:\$J\$5001=\$D\$2)*NOT(ISBLANK (Data!\$A\$2:\$A\$5001)))

This returns 58.

3. ## Re: problem with excel formula (2003)

Thank you very much!! You are the man!

#### Posting Permissions

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