+ Reply to Thread
Results 1 to 13 of 13

Sumproduct not working

  1. #1
    Registered User
    Join Date
    10-26-2012
    Location
    Glasgow should be in UK
    MS-Off Ver
    Excel 2010
    Posts
    72

    Sumproduct not working

    Hi There,

    I tried few times and see no issue in my formula for sumproduct. Can anybody help me. It looks a silly mistake but clearly I'm not looking properly

    =SUMPRODUCT(--('raw data pcn sidem'!$P$1:$P$54629=F11),--('raw data pcn sidem'!$L$1:$L$54629='reps summary sheet'!Q10))

    What I want is the first to look for a list and compare the name against in a main sheet( where result is displayed) and similar in the next part and just give me a total count..I'm using as array formula..

    Please help!

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Sumproduct not working

    Quote Originally Posted by nicci113 View Post
    Hi There,

    .I'm using as array formula..

    Please help!
    No need to use as an ARRAY formula. Just click ENTER to confirm the formula.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    10-26-2012
    Location
    Glasgow should be in UK
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Sumproduct not working

    not working that way either

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,094

    Re: Sumproduct not working

    Looks OK, assuming that F11 is on the active sheet (the same sheet as the formula) and the sheet references are correct for the other ranges and cells.

    When you say, "not working", what does that mean? We probably need to see a sample workbook to check the actual data.

    SUMPRODUCT works with arrays but it is NOT an Array Formula, that is, you do NOT need to use Ctrl-Shift-Enter to commit it.


    Regards, TMS
    Last edited by TMS; 06-02-2014 at 07:05 AM.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Sumproduct not working

    Before you upload a small sample workbook, give a try to this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Sumproduct not working

    I believe you are missing one more parameter in the SUMPRODUCT
    If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.

    If your requirement has been solved please mark your thread as Solved.
    In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".

    Kindly use [FORMULA] or [CODE] tags when posting your code.

    Regards,
    Sarang

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,094

    Re: Sumproduct not working

    I believe you are missing one more parameter in the SUMPRODUCT

    What would that be?

  8. #8
    Registered User
    Join Date
    10-26-2012
    Location
    Glasgow should be in UK
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Sumproduct not working

    attached!

    what I m doing is counting the nyumber of notice in sheet 1 against number of people in sheet 2 and getting a total..

    hope it not diffucult to understand
    Attached Files Attached Files

  9. #9
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,964

    Re: Sumproduct not working

    Hi,

    The two sheet1 ranges need to be the same number of rows. In addition the $Q$10 reference should be relative as far as the column ref is concerned. i.e.


    Q11: copied across & down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  10. #10
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Sumproduct not working

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    10-26-2012
    Location
    Glasgow should be in UK
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Sumproduct not working

    aww.. i knew its sillly saved a time bloody spending 1 hr on this..

    Thanks a lot all of you and really apperciate your assistance.. S

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,094

    Re: Sumproduct not working

    See the attached. Usually better to keep the structure the same but hopefully you can work with the attached updated example.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    10-26-2012
    Location
    Glasgow should be in UK
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Sumproduct not working

    Thanks yes, but the another issue ws that my other sheet had N/a and it was also causing the issue just updated. Sometime the wee things are more of a pain

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sumproduct = not working the way I want : )
    By Runnin L8 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-05-2013, 04:22 PM
  2. [SOLVED] SUMPRODUCT not working
    By User3 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-01-2013, 06:31 AM
  3. Sumproduct Not Working
    By carl in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-15-2006, 02:15 PM
  4. [SOLVED] sumproduct not working
    By BorisS in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2006, 04:30 PM
  5. [SOLVED] SUMPRODUCT Not Working
    By Scott in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-09-2005, 10:50 AM

Tags for this Thread

Bookmarks

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