Why sumproduct formula return result 0

HI..IM NEW IN THIS FORUM AND IN EXCEL GENERALY

I have a little problem with sumproduct formula in excel 2007...
=SUMPRODUCT(F4:F35=B82,G4:G35=F98,J4:J35) and i get the 0 result

but when i put manualy this formula i get the proper result
=SUMPRODUCT((F4:F35=B82)*(G4:G35=F98)*(J4:J35))

my colums are TEXT ---TEXT---NUMBER

so is there a way not to do this manualy ?!

THNX

Hi and welcome to the forum.

What you mean "..when i put manualy this formula i get .."

This is the corect type for the SUMPRODUCT function..

=SUMPRODUCT((F4:F35=B82)*(G4:G35=F98)*(J4:J35))

ARIA1,


Forum Rules
Please read and adhere to these simple rules!

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more days have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

Originally Posted by ARIA1

HI..IM NEW IN THIS FORUM AND IN EXCEL GENERALY

I have a little problem with sumproduct formula in excel 2007...
=SUMPRODUCT(F4:F35=B82,G4:G35=F98,J4:J35) and i get the 0 result

but when i put manualy this formula i get the proper result
=SUMPRODUCT((F4:F35=B82)*(G4:G35=F98)*(J4:J35))

my colums are TEXT ---TEXT---NUMBER

so is there a way not to do this manualy ?!

THNX

Since you're using Excel 2007 you can use the SUMIFS function...

=SUMIFS(J4:J35,F4:F35,B82,G4:G35,F98)

Tony, please read forum rules:

7. Don't ignore requests by Administrators, Moderators, or senior members of the forum. If you are unclear about their request or instructions, then send a private message to them asking for help. Do not post a reply in a thread where such a request (e.g., title change, code tags) is pending.

Originally Posted by zbor

Tony, please read forum rules:

7. Don't ignore requests by Administrators, Moderators, or senior members of the forum. If you are unclear about their request or instructions, then send a private message to them asking for help. Do not post a reply in a thread where such a request (e.g., title change, code tags) is pending.

This message has been deleted by zbor.

Are you serious?

I'm outta here!

Unfortunately I have a bad sense of humor

From mine point of view it's a formula name. Like naming a thread: IF.
As per the rules it should describe a problem.

And there would be much more if we don't interfere. But here, I've change a title so this saga can end.

@ARIA1:
SUMPRODUCT formula: =SUMPRODUCT(A1:A4>0) will return you a 0 even if all values in range are greater than 0.
That's because, if you evaluate formula you will get: =SUMPRODUCT({TRUE,FALSE,TRUE,TRUE})
Since they are all text values SUMPRODUCT formula ignore them and final result is 0.

To get propper result you need to convert those TRUE/FALSE values into numbers (1/0).
And that you can do either by multiply by a number (for example 1), or with double unary -- or something else.

In =SUMPRODUCT(1*(A1:A4>0)) will return you result 3 (if there were three values greater than zero) and that's what you did in second formula so that's the reason you get desired result.
Because:
=SUMPRODUCT(1*(A1:A4>0))
=SUMPRODUCT(1*(TRUE,FALSE,TRUE,TRUE))
=SUMPRODUCT({1,0,1,1})
=3

I think the problem you are facing is your data type
Try using the unary operators

Please Login or Register  to view this content.

This forces TRUE, FALSE to 1, 0 then the formula can multiply the sums.

If you use this form of the formula ...

Please Login or Register  to view this content.

TRUE and FALSE are automatically calculated (forced) as 1, 0

Originally Posted by ARIA1

Do i need every time to correct the formula >!

Hello ARIA1, I'm not sure what you mean by that comment, if you use the formula that Marcol suggests then it should work...then you can use that formula every time.

In Excel 2007 you might prefer to use SUMIFS, i.e.

=SUMIFS(J4:J35,F4:F35,B82,G4:G35,F98)