Excel 2007 : Sumproduct Help

**emmarky** · 06-12-2009, 08:19 AM

I have been using the following formula to calculate a 4 year running programme and it works fine. The trouble i have is when i try to use sumproduct to make this formula myself i get the wrong formula so i am having to copy and paste the correct one from a spreadsheet that works.

The formula i need and use is
=SUMPRODUCT(--('Site Listing'!$B$1:$B$600="TESCO EXPRESS"),--('Site Listing'!$K$1:$K$600="CLEAN ONLY"),'Site Listing'!$L$1:$L$600)

The formula i creat when trying to use excell is
=SUMPRODUCT('Site Listing'!B1:B600="TESCO EXPRESS",'Site Listing'!K1:K600="CLEAN ONLY",'Site Listing'!L1:L600)

I can clearly see what is missing from the formula but cannot figure out what step or where i am going wrong by using excell to create it??

**NBVC** · 06-12-2009, 08:47 AM

I don't really understand the problem...

Are you enclosing the 1st 2 arguments in parentheses and prefixing each with a double unary (--) ? Like the first formula...

**emmarky** · 06-12-2009, 09:05 AM

The problem i have is that without manually typing in the code using my keyboard i cannot figure out how to create the same formula that was given to me by a user on this forum using the Excell sumproduct.

The formula does not work without the code being

=SUMPRODUCT(--('Site Listing'!$B$1:$B$600="TESCO EXPRESS"),--('Site Listing'!$K$1:$K$600="CLEAN ONLY"),'Site Listing'!$L$1:$L$600)

The best i can manage using excell is

=SUMPRODUCT('Site Listing'!B1:B600="TESCO EXPRESS",'Site Listing'!K1:K600="CLEAN ONLY",'Site Listing'!L1:L600)

So it is missing all the (-- and the $ in the formula, Unless you are suppose to manually type in the (-- and the $ in the formula ?

**NBVC** · 06-12-2009, 09:11 AM

Yes, when you first create a formula, you have to type in the brackets, --, etc... the only thing you don't need to type is the cell/range references (and only if you point to them during the construction of your formula)... the F4 key toggles the $ signs if you do not want to type them in.

**emmarky** · 06-12-2009, 09:27 AM

Originally Posted by NBVC

Yes, when you first create a formula, you have to type in the brackets, --, etc... the only thing you don't need to type is the cell/range references (and only if you point to them during the construction of your formula)... the F4 key toggles the $ signs if you do not want to type them in.

Thats were i have been going wrong, i thought it may be the case but kept telling myself that it was automatically worked out...

Thanks.

Do you have a link to what brackets etc do in excell for reference sake..

**NBVC** · 06-12-2009, 09:30 AM

Well, that's really Excel 101.... you would need to get a book on Excel for Beginners,

Here is one link:

http://spreadsheets.about.com/od/exc...readsheets.htm

or google Excel 101 or Excel for Beginners or Excel Tutorials.. or get Excel for Dummies book.