+ Reply to Thread
Results 1 to 7 of 7

If Function - Counting occurences

  1. #1
    Registered User
    Join Date
    05-24-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    76

    Question If Function - Counting occurences

    Hi, would appreciate some help with this.

    Column B has the 'Sector Names'. I would like the number of times they appear in 'Sector' column M to be
    shown under 'Number of these sectors' column C.
    I would only require it to calculate the number of Sectors if the 'Status' cell shows 'Open' or 'Trade Open - Risk-Free'.

    If anything else is shown in 'Status' then I won't require it to count the Sectors.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: If Function - Counting occurences

    Hi kgtrader,

    I think you are looking for this beast of a formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    SumProduct And and Or.xlsx
    In SumProduct the "*" means AND and the "+" means OR.
    See the attached in the above link.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: If Function - Counting occurences

    Here's just another option:
    In cell C8, then copy down.

    =COUNTIFS(Table1[Sector],B8)-SUM(COUNTIFS(Table1[Status],{"Open","Trade Open - Risk-Free"},Table1[Sector],B8))

  4. #4
    Registered User
    Join Date
    05-24-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    76

    Re: If Function - Counting occurences

    Gregb11 that works well thanks but how do you refer to another sheet in this formula as I would like this table to appear in another sheet.

  5. #5
    Registered User
    Join Date
    05-24-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    76

    Re: If Function - Counting occurences

    Quote Originally Posted by MarvinP View Post
    Hi kgtrader,

    I think you are looking for this beast of a formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Attachment 679976
    In SumProduct the "*" means AND and the "+" means OR.
    See the attached in the above link.
    This works fine on this sheet thanks. But I used this in another excel workbook and I changed the cell references appropriately
    but it does not work in that workbook.
    Is there anything I need to keep in mind when using this formula in another workbook to make it work?

  6. #6
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: If Function - Counting occurences

    That's the nice thing about Excel Tables, it doesn't matter what sheet it's on, it's already referencing the Table Name.

  7. #7
    Registered User
    Join Date
    05-24-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    76

    Re: If Function - Counting occurences

    Gregb11, thanks for your help. The formula was doing the opposite of what I wanted so switched
    the names in the quotation and works fine.
    Your advice on the table referencing was also useful.

+ 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. [SOLVED] Counting occurences
    By RickMcc in forum Excel General
    Replies: 20
    Last Post: 03-21-2018, 08:18 AM
  2. Counting no of occurences.
    By trifixion1 in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 03-16-2011, 01:53 PM
  3. Counting the max occurences of events
    By alfredkri in forum Excel General
    Replies: 12
    Last Post: 01-25-2011, 03:08 AM
  4. counting occurences
    By joeponcho in forum Excel General
    Replies: 2
    Last Post: 01-16-2008, 09:44 AM
  5. Counting the occurences
    By riomarde in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-27-2006, 04:10 PM
  6. Counting Occurences
    By Tomac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-16-2006, 12:24 AM
  7. Counting Occurences
    By Pete in forum Excel General
    Replies: 7
    Last Post: 05-02-2005, 04:06 PM

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