+ Reply to Thread
Results 1 to 7 of 7

COUNTIFS and SUMIFS with a condition to exclude rows whose value is in another range

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    COUNTIFS and SUMIFS with a condition to exclude rows whose value is in another range

    Hi everyone - looking for some guidance with a formula I'm trying to write.

    I have a portfolio sheet with multiple rows and the following ranges (among others, but these are the relevant ones):
    rngTicker - ticker code / unique ID for the stock
    rngPosition - whether that ticker is long or short (or has no position "=None")
    rngValue - P&L for each ticker at the current date

    In that sheet, I count the number of profitable trades in a formula like this:
    =SUM(COUNTIFS(rngPosition,{"Long","Short","None"},rngValue,">0"))

    I also sum the total profitable value of tickers with positions like this:
    =SUM(SUMIFS(rngValue,rngPosition,{"Long","Short","None"},rngValue,">0"))
    ... and the non-profitable value has the condition "<0".

    In another sheet (called Control) is a list of tickers to exclude from the P&L calculations - this is rngExclude.

    I can count the number of values in rngTicker that are also in rngExclude with this formula:
    =SUMPRODUCT(--(ISNUMBER(MATCH(rngTicker,rngExclude,0))))

    But what I cannot work out is how to count and sum these ranges with the added condition that the ticker in rngTicker in the portfolio sheet does not exist in the rngExclude in the control sheet.

    Any guidance on how to combine these would be appreciated. Thank you in advance.

    MM.
    MatrixMan.
    --------------------------------------
    If this - or any - reply helps you, remember to say thanks by clicking on *Add Reputation.
    If your issue is now resolved, remember to mark as solved - click Thread Tools at top right of thread.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,682

    Re: COUNTIFS and SUMIFS with a condition to exclude rows whose value is in another range

    it sounds like you are have ticker numbers ??? if so I would wonder if adding in a minus function going against the ticker numbers that match in both but are under the exclude in the control tab.
    Purely a guess since there is no workbook uploaded and it is build a formula by guessing, it appears you have multiple table names which can add to the confusion since some of us would have to see the table names and ranges.
    BTW, we value your privacy but a location of Earth isn't helpful since different locals have different excel parameters, for example they treat dates differently and different delimiters.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: COUNTIFS and SUMIFS with a condition to exclude rows whose value is in another range

    Thanks Sam ... tickers are unique stock market codes; it doesn't really matter what they are, only to note that they are unique identifiers for a record. I can't upload the actual file but I will create a small sample file and send it tomorrow to make it easier (I too dislike it when folks do that; I should know better - sorry).

  4. #4
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: COUNTIFS and SUMIFS with a condition to exclude rows whose value is in another range

    Sample file attached for reference.
    Attached Files Attached Files

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,682

    Re: COUNTIFS and SUMIFS with a condition to exclude rows whose value is in another range

    I'm having a bit of trouble looking back and forth between your post #1 and your uploaded workbook. For example, your sum/countifs rngPosigion matches the formula you have in C3 through C6 so that matches. But your second formula, the sum/sumifs rngValue which in your name manager refers to columns D and E but your formula points that portion at column B though the rest matches (rngPosition matches col A). So when you mention that you want to exclude the control tickers from the P&L formula I'm having trouble seeing which formula you are using for the P&L calculations.

    I believe a formula can be written to exclude those but right now I'm not seeing, when I use trace dependents, anywhere where the values in col F of portfolio are being used.
    Right now only columns A, B and C in the portfolio tab are being used.

    So, if you are wanting to remove the values from, for example, the long profit count in cell C3, simple way, for now that is, I just put a vlookup in col H
    =IFERROR(VLOOKUP(F12,Control!$D$5:$D$10,1,FALSE),"")
    dragged down, then altered the sum countif in C3 to no longer count those values in col B represented by their tickers from col F (using the vlookup in co H) like so...
    =SUM(COUNTIFS($A:$A,{"Long","Short","None"},$B:$B,">0",H:H,""))

    now if that is the direction you are wanting to go, I believe a more streamlined version can be developed.

  6. #6
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: COUNTIFS and SUMIFS with a condition to exclude rows whose value is in another range

    Hi Sam. Thank you for taking the time to respond but in the end I didn't come up with a built-in function combo that could do what i needed so I wrote a UDF and did it in VBA, which is working perfectly. Probably too specific an issue.

    Thanks again.
    MM

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,682

    Re: COUNTIFS and SUMIFS with a condition to exclude rows whose value is in another range

    Glad you got it resolved, sorry I couldn't help more. Thank you for the rep though!!

+ 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. Used Range: SUMIFS vs COUNTIFS
    By XOR LX in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-30-2020, 04:47 PM
  2. SUMIFS, exclude range of criteria
    By ABSTRAKTUS in forum Excel General
    Replies: 1
    Last Post: 02-06-2020, 10:44 AM
  3. [SOLVED] Identifying hidden rows to exclude from sumifs
    By Cbird in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-29-2017, 12:24 AM
  4. [SOLVED] COUNTIFS forumulas that can exclude criteria in a range
    By jholiday78 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-05-2015, 12:00 PM
  5. [SOLVED] Countifs and sumifs using Name Range
    By anh03 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-23-2015, 11:19 PM
  6. [SOLVED] I can countifs a range but not sumifs
    By port in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-18-2014, 07:06 PM
  7. [SOLVED] =sum(countifs(range,{not this, not this, nor this} -How to exclude criteria in this manner
    By nscarritt in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-26-2014, 04:00 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