# Problem in sumproduct

1. ## Problem in sumproduct

i am having problem in sumproduct due to #N/A error in my data. file is attached
i need help plz do the needful

Regards
Sajid

2. ## Re: Problem in sumproduct

Perhaps try using COUNTIFS, i.e.

=COUNTIFS(A2:A10,D3,B2:B10,">=0")

3. ## Re: Problem in sumproduct

no i have to sum the values with given criterias

4. ## Re: Problem in sumproduct

1 get rid of na
like this

=IF(ISNA(VLOOKUP(A2,\$D\$3:\$E\$5,2,FALSE)),0,VLOOKUP(A2,\$D\$3:\$E\$5,2,FALSE))
2 the sum product can then be
=SUMPRODUCT(--(A2:A10=D3),--(B2:B10>=0))

5. ## Re: Problem in sumproduct

Or, after taken care of the NA one way or another ( I'm not a big fan of the IF(ISNA...) technique) you can use a Pivot Table to summarize

6. ## Re: Problem in sumproduct

Use SUMIF if you want to SUM values:

=SUMIF(A2:A10, D3, B2:B10)

7. ## Re: Problem in sumproduct

try this:
``Please Login or Register  to view this content.``
Is that something you can work with?

8. ## Re: Problem in sumproduct

Or using an array formula:

``Please Login or Register  to view this content.``
confirm with Ctrl-Shift-Enter.

Cheers,

9. ## Re: Problem in sumproduct

MS Office Version:Excel 2003

10. ## Re: Problem in sumproduct

Originally Posted by Ron Coderre
try this:
``Please Login or Register  to view this content.``
Is that something you can work with?

thanks its working now

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1