+ Reply to Thread
Results 1 to 6 of 6

Sumproduct formula issue

  1. #1
    Registered User
    Join Date
    01-29-2007
    Posts
    18

    Sumproduct formula issue

    I am having an issue with my sumproduct formula.

    I have attached a copy of my spreadsheet. I am using the sumproduct as my form for reporting up to the minute results.

    In the Cascade Actual Summary tab, I used the sumproduct formula to report results from the cascade detail tab. When someone puts in the proper criteria it is supposed to automatically fill out my Summary tab to show what has been completed. In the far right 2 columns you will see under completed the new column and the % complete. This is the sumproduct formula that isn't working. It should be counting these but it isn't.

    I was hoping for another set of eyes.

    Thank you in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,677
    Currently the formulas in F3 down will always return zero because you are trying to count when column D = 2800 and when column D = 4000. The way the formula is written you'll only get a count when both of these conditions is TRUE, which can't happen.

    Also as the 4000 and 2800 entries are numeric these should have quotes. Perhaps you want something like

    =SUMPRODUCT(('Cascade Detail'!$C$2:$C$7004=A3)*ISNUMBER(MATCH('Cascade Detail'!$D$2:$D$7004,{2800,4000},0))*('Cascade Detail'!$H$2:$H$7004<>"")*('Cascade Detail'!$I$2:$I$7004<>""))

    In F3 this will count when column C is "Birmingham", column D is 2800 or 4000 and both column H and column I are not empty. IS that what you require?

  3. #3
    Registered User
    Join Date
    01-29-2007
    Posts
    18
    Yes, that is what I am looking for.

    Let me try the code you put out there.

  4. #4
    Registered User
    Join Date
    01-29-2007
    Posts
    18
    It didn't work for me for some reason. I formatted those columns to numbers but not working still.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,677
    Are you using the formula I posted? When I said the numbers should have quotes I meant to say shouldn't, so the formula I posted was correct

    Are you trying the formula with the data you posted? As your data stands I don't think you have any rows that fulfil the criteria so it will return zero.

  6. #6
    Registered User
    Join Date
    01-29-2007
    Posts
    18
    Yeah, I put it in exactly how you had it without the quotes around the numbers.

    I then put in some dummy data in the columns it counts to make it count and it didn't work.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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