Results 1 to 9 of 9

Thread: transforming sumproduct to countif(s)

  1. #1

    transforming sumproduct to countif(s)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    hi,

    1. how would i transform this formula
    =SUMPRODUCT(($C$4:$C$32600="W")*(G$5:G$32601="PICK")*(IFERROR($J$3:$J$32599-$C$7:$C$32603;2)<=2/24);G$4:G$32600)
    to
    countif with same criteria?

    i want to count the same numbers that it before sumed.

    i actualy want to count those numbers that have "W" or "L" so i would have to put "w or l".
    2. how do i put "w or l" insted of "w" in sumproduct and countifs?
    Book1.xlsx

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Unless you add a helper column for the IFERROR() part, then it'd be difficult to convert that formula to a COUNTIFS formula...

    Having said that, to answer your second question, try:

    =SUMPRODUCT((($C$4:$C$32600="W")+($C$4:$C$32600="L"))*(G$5:G$32601="PICK")*(IFERROR($J$3:$J$32599-$C$7:$C$32603,2)<=2/24),G$4:G$32600)


  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,829
    Articles
    0
    Excel Version
    O365
    That seems awfully mixed up data there, but that formula can be simplified a bit

    =SUMPRODUCT((($C$4:$C$32600={"W","L"}))*(G$5:G$32601="PICK")*(IFERROR($J$3:$J$32599-$C$7:$C$32603,2)<=2/24),G$4:G$32600)

  4. #4
    what would the formula look if i added a helper column?

  5. #5
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,829
    Articles
    0
    Excel Version
    O365
    It's a SUMIFS not COUNTIFS, and would be like

    =SUM(SUMIFS(G$4:G$32600,$C$4:$C$32600={"W","L"},G$5:G$32601="PICK",H$4:H$32600,"<="&2/24))

    or

    =SUMIFS(G$4:G$32600),$C$4:$C$32600="W",G$5:G$32601="PICK",H$4:H$32600,"<="&2/24)
    +
    SUMIFS(G$4:G$32600),$C$4:$C$32600="L",G$5:G$32601="PICK",H$4:H$32600,"<="&2/24)

    where H4 copied down isIFERROR($J3-$C7,2)

  6. #6
    Book1.xlsx
    this formula returns an error.

    =SUM(SUMIFS(G$4:G$32600;$C$4:$C$32600={"W","L"};G$5:G$32601="PICK";L$3:L$32599;"<="&2/24))

    maybe its my regional settings.

    i changed , to ; because i have different regional settings. should i change something else?

  7. #7
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,829
    Articles
    0
    Excel Version
    O365
    Sorry, I made a couple of errors

    =SUM(SUMIFS(G$4:G$32600;$C$4:$C$32600;{"W","L"};G$5:G$32601;"PICK";L$3:L$32599;"<="&2/24))

  8. #8
    this formula still doesnt work for me. did you test it on my file?

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    It works for me. My result is 43.63.


Posting Permissions

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