Consulting

Results 1 to 14 of 14

Thread: Sumproduct/Subtotal > issue

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Sumproduct/Subtotal > issue

    Hi there,

    I'm having a problem with the following formula

    "=SUMPRODUCT((Completed_Weekly[Days CCD > OCD]=">0")*(Completed_Weekly[REQUEST_TYPE]="NEW")*(SUBTOTAL(103,OFFSET(Completed_Weekly[REQUEST_TYPE],ROW(Completed_Weekly[REQUEST_TYPE])-MIN(ROW(Completed_Weekly[REQUEST_TYPE])),0))))"
    I have isolated the issue to the (Completed_Weekly[Days CCD > OCD]=">0") part of it but cant seem to get it to work.

    I have tried (Completed_Weekly[Days CCD > OCD]=">0") and (Completed_Weekly[Days CCD > OCD]>0) with no success

    any ideas?

    thanks

    Paddy
    Last edited by Aussiebear; 04-27-2023 at 12:27 PM. Reason: Added code tags

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    It's SUMPRODUCT not SUMIFS

    PHP Code:
    =SUMPRODUCT((Completed_Weekly[Days CCD OCD]>0)
    *(
    Completed_Weekly[REQUEST_TYPE]="NEW")
    *(
    SUBTOTAL(103,OFFSET(Completed_Weekly[REQUEST_TYPE],ROW(Completed_Weekly[REQUEST_TYPE])-MIN(ROW(Completed_Weekly[REQUEST_TYPE])),0)))) 
    Last edited by Aussiebear; 04-27-2023 at 12:27 PM. Reason: Added code tags
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    It's SUMPRODUCT not SUMIFS

    =SUMPRODUCT((Completed_Weekly[Days CCD > OCD]>0)
    *(Completed_Weekly[REQUEST_TYPE]="NEW")
    *(SUBTOTAL(103,OFFSET(Completed_Weekly[REQUEST_TYPE],ROW(Completed_Weekly[REQUEST_TYPE])-MIN(ROW(Completed_Weekly[REQUEST_TYPE])),0))))
    I was using SUMPRODUCT in my original formulas? The above still gives me an incorrect count of 120 rather than 1, the filtered data is shown below

    REQUEST_TYPE Days CCD > OCD Days CCD < OCD
    NEW 50
    NEW 13
    NEW 13
    NEW 27
    NEW 13
    NEW 13
    NEW 13
    NEW 13
    NEW 13
    NEW 13
    NEW 13
    NEW 13
    NEW 13
    NEW 293
    NEW 36

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by paddysheeran View Post
    I was using SUMPRODUCT in my original formulas? The above still gives me an incorrect count of 120 rather than 1, the filtered data is shown below
    My point was that SUMPRODUCT does not use the synatx of SUMIFS, so instead of

    PHP Code:
    (Completed_Weekly[Days CCD OCD]=">0"
    you should use

    PHP Code:
    (Completed_Weekly[Days CCD OCD]>0
    Last edited by Aussiebear; 04-27-2023 at 12:28 PM. Reason: Added code tags
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I get a result of 2 not 120 with that data, but that is probably because you have extra rows at the bottom of the table which I don't have.

    Nonetheless, I think your problem lies in the fact that you are using the the whole column of REQUEST_TYPE in the OFFSET function, and it counts the number of rows in the column from that particular row down, not just whether that row. If you force it to use just the first cell, it seems to be correct

    PHP Code:
    =SUMPRODUCT((Completed_Weekly[Days CCD OCD]>0)
    *(
    Completed_Weekly[REQUEST_TYPE]="NEW")
    *(
    SUBTOTAL(103,OFFSET(INDEX(Completed_Weekly[REQUEST_TYPE],1,1),ROW(Completed_Weekly[REQUEST_TYPE])-MIN(ROW(Completed_Weekly[REQUEST_TYPE])),0)))) 
    Last edited by Aussiebear; 04-27-2023 at 12:28 PM. Reason: Added code tags
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    PHP Code:
    =SUMPRODUCT((Completed_Weekly[Days CCD OCD]>0)
    *(
    Completed_Weekly[REQUEST_TYPE]="NEW")
    *(
    SUBTOTAL(103,OFFSET(INDEX(Completed_Weekly[REQUEST_TYPE],1,1),ROW(Completed_Weekly[REQUEST_TYPE])-MIN(ROW(Completed_Weekly[REQUEST_TYPE])),0)))) 
    Still not working I'm afraid. getting a count of 15
    Last edited by Aussiebear; 04-27-2023 at 12:29 PM. Reason: Added code tags

  7. #7
    are you working with Table?
    can you use CountIfs():

    PHP Code:
    =COUNTIFS(Completed_Weekly[[#All],[REQUEST_TYPE]],"NEW",Completed_Weekly[[#All],[Days CCD > OCD]],"<>") 
    Last edited by Aussiebear; 04-27-2023 at 12:29 PM. Reason: Added code tags

  8. #8

  9. #9
    is this what you meant?
    Attached Files Attached Files

  10. #10
    So in the attached the original formula result (E4) will change dependent on the location selected in the slider.

    There is only one row where Days > OCD is > 0 for "New" Request Types

    When I select Location 1 there should be a count of one (Counts 9), selecting location 2 in the slider should result in a count of zero (Counts 5)
    Attached Files Attached Files

  11. #11
    see this, you can convert the "range" and replace it with your "named range"
    Attached Files Attached Files

  12. #12
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    It's bad data, if you clear all of those seemingly blank cells in the Days CCD > OCD, it works fine.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  13. #13
    [Days CCD > OCD]<>"" did the trick. Thanks for all the help.

  14. #14
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    If you are filtering by the same criteria as the count criteria, hidden rows are irrelevant, so just use COUNTIFS

    PHP Code:
    =COUNTIFS(Completed_Weekly_1[Days CCD OCD],">0",Completed_Weekly_1[REQUEST_TYPE],"NEW"
    Last edited by Aussiebear; 04-27-2023 at 12:30 PM. Reason: Added code tags
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •