Sumproduct Returning Count of criteria not Sum

[Swinden]

I've tried this several different ways. A formula written as;
=SUMPRODUCT(($B$5:$B$10="Apple")*(C5:C10<>"-"))

Returns the count of cells in the range with the name Apple and not equal to-

So in the image below, in column C, there are no cells with a numerical value, so the count is returned as 1, where i want the sum to be 6

untitled.JPG

Ive also tried
=SUMPRODUCT(($B$5:$B$10="Apple")*--(C5:C10<>"-"))

but get the same result.

Any ideas?

[TMS]

Try:

Please Login or Register  to view this content.

[Pete_UK]

I presume you are typing the hyphen in (to represent zero). To sum column C with those criteria, you should change the formula to this:

=SUMPRODUCT(($B$5:$B$10="Apple")*(C5:C10<>"-"),C5:C10)

Hope this helps.

Pete

[southward]

Hi,

For "Sum Apple" try:

Please Login or Register  to view this content.

For "Count" try:

Please Login or Register  to view this content.

Hope this is helpful.

Cheers

[Tony Valko]

Have you tried...

=SUMIF($B5:$B10,"Apple",C5:C10)

[Swinden]

SOLVED! Thanks guys... I hate when you stare at something so long and the answer is right in front of you the whole time....

[Tony Valko]

You're welcome. Thanks for the feedback!

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.