+ Reply to Thread
Results 1 to 13 of 13

Problem with SUMIFS

  1. #1
    Registered User
    Join Date
    09-17-2021
    Location
    Gdynia
    MS-Off Ver
    2016
    Posts
    66

    Problem with SUMIFS

    Guys I have a macro where I would like it to select <0 results for WYCENA_CVA but only for the IRS item from the INSTRUMENT column. I tried to write the code below, but I get the type mismatch error, where is my fault, hwo to fit it ?

    Please Login or Register  to view this content.
    cross - post :https://stackoverflow.com/questions/...72144_70692678
    Last edited by Palucci; 01-13-2022 at 03:37 AM.

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Problem with SUMIFS

    There's code missing here. What is "IRS" set to? From your description, it should be "INSTRUMENT" but it's not declared in this snippet. Regardless, your SumIfs() arguments are wrong:

    SumIfs([Sum Range], [Criteria Range], [Criteria], [Criteria Range], [Criteria]) so perhaps:

    Please Login or Register  to view this content.
    WBD
    Office 365 on Windows 11, looking for rep!

  3. #3
    Registered User
    Join Date
    09-17-2021
    Location
    Gdynia
    MS-Off Ver
    2016
    Posts
    66

    Re: Problem with SUMIFS

    Thanks, but now i got error :

    Object variable or With block variable not set
    Dim rCol As Range
    Dim rCriteria As Range
    Dim LastRow As Long
    Dim LastCol As Long
    Dim rCol1 As Range

    Attachment 763230
    Last edited by Palucci; 01-13-2022 at 05:43 AM.

  4. #4
    Registered User
    Join Date
    09-17-2021
    Location
    Gdynia
    MS-Off Ver
    2016
    Posts
    66

    Re: Problem with SUMIFS

    Now i resolved problem with object, But my code does not do anything now

  5. #5
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Problem with SUMIFS

    Can you attach an example? Trying to figure this out without concrete data or context is difficult.

    WBD

  6. #6
    Registered User
    Join Date
    09-17-2021
    Location
    Gdynia
    MS-Off Ver
    2016
    Posts
    66

    Re: Problem with SUMIFS

    Quote Originally Posted by WideBoyDixon View Post
    Can you attach an example? Trying to figure this out without concrete data or context is difficult.

    WBD
    Below is some exampel date , is there ok for you or need some else to test ?
    INSTRUMENT WYCENA_po_CVA_DVA
    FX_FORWARD 15864,3029
    FX_FORWARD 2953,771801
    FX_FORWARD 20311,10553
    CIRS 23751398,88
    CIRS 253335282,9
    IRS 3556722,5
    CIRS 88344164,79
    CIRS 70675331,82
    CIRS 17550451,8
    CIRS 29973434,85
    CIRS 34189895,51
    Last edited by Palucci; 01-13-2022 at 07:34 AM.

  7. #7
    Registered User
    Join Date
    09-17-2021
    Location
    Gdynia
    MS-Off Ver
    2016
    Posts
    66

    Re: Problem with SUMIFS

    INSTRUMENT WYCENA_po_CVA_DVA
    IRS 15864,3029
    IRS 2953,771801
    CIRS 23751398,88
    Last edited by Palucci; 01-13-2022 at 06:47 AM.

  8. #8
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Problem with SUMIFS

    Still lots missing but I think I pieced some of it together at least:

    Please Login or Register  to view this content.
    Perhaps the issue you have is that you're asking to sum values less than zero (see above highlight). You could change this to ">0" to get a positive sum?

    WBD

  9. #9
    Registered User
    Join Date
    09-17-2021
    Location
    Gdynia
    MS-Off Ver
    2016
    Posts
    66

    Re: Problem with SUMIFS

    With attachment
    Attached Files Attached Files

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Problem with SUMIFS

    Rory

  11. #11
    Registered User
    Join Date
    09-17-2021
    Location
    Gdynia
    MS-Off Ver
    2016
    Posts
    66

    Re: Problem with SUMIFS

    Quote Originally Posted by WideBoyDixon View Post
    Still lots missing but I think I pieced some of it together at least:

    Please Login or Register  to view this content.
    Perhaps the issue you have is that you're asking to sum values less than zero (see above highlight). You could change this to ">0" to get a positive sum?

    WBD
    Thanks its working ! But for example if I have two conditions in the instrument as in the following code example. Gives me the result 0, and I mean to sum up the results for a position where it is CIRS and FX together.

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    09-17-2021
    Location
    Gdynia
    MS-Off Ver
    2016
    Posts
    66

    Re: Problem with SUMIFS

    Quote Originally Posted by rorya View Post
    Sorry for that , im missing crss-posting

  13. #13
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Problem with SUMIFS

    SUMIFS criteria must *all* be met so you're asking for the INSTRUMENT to be "CIRS" and "FX_FORWARD" at the same time - it's going to return zero. You could implement an *or* condition in code like this:

    Please Login or Register  to view this content.
    WBD

+ 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. SUMIFS problem
    By sinweaver in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-04-2020, 02:29 AM
  2. Sumifs problem
    By soul24rage in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-03-2014, 11:34 PM
  3. [SOLVED] Sumifs problem
    By hammer2 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-20-2014, 03:21 AM
  4. SUMIFS problem
    By chris-streeter in forum Excel General
    Replies: 6
    Last Post: 10-30-2013, 09:55 AM
  5. Sumifs problem
    By kim296 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-02-2013, 02:56 PM
  6. [SOLVED] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2012, 04:26 AM
  7. Excel 2007 : Sumifs problem
    By Bee&H in forum Excel General
    Replies: 2
    Last Post: 11-30-2011, 12:29 PM

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