+ 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
    43

    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
    15,194

    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
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    674

    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))
    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

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

    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
    43

    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
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    674

    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
    43

    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. [SOLVED] 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