+ Reply to Thread
Results 1 to 6 of 6

Count Frequency IF (this) OR (that) for events

  1. #1
    Registered User
    Join Date
    08-19-2021
    Location
    London
    MS-Off Ver
    O365
    Posts
    9

    Post Count Frequency IF (this) OR (that) for events

    Hi everyone, I'm attempting to count the frequency of events which I managed to get set up. I was rather pleased with it until I realised not everyone works every day. To track this accurately I'm now trying to count the frequency of IF (this) OR (that).

    In my example I am counting the frequency of "S" events, but I would like it to count the frequency of "S" and "X" events (combined - if this is possible?).

    S S S Blank S S S would be 2 events of "S" and 6 entries of "S"
    S X S Blank S X S should be 2 events of "S OR X" and 4 entries of "S", 2 entries of "X". - I can do the COUNTIF for the entries, but I'm having a lot of problems with counting the frequency of events.

    S X S Blank S X S - with my current formula this counts 4 events of "S"; but I would like this to be counted 2 events of "S OR X".

    I have attached a sample worksheet that I hope makes more sense!

    Current Frequency (array) formula:
    {=SUM(IF(FREQUENCY(IF(B3:J3="S",COLUMN(B3:J3)),IF(B3:J3<>"S",COLUMN(B3:J3)))>0,1))} (This counts the frequency of "S" events over B3 - J3)

    Failed Frequency IF OR Formula attempt:
    {=SUM(IF(FREQUENCY(IF(OR(B3:J3="S",B3:J3="X"),COLUMN(B3:J3)),IF(OR(B3:J3<>"S",B3:J3<>"X"),COLUMN(B3:3)))>0,1))}

    Failed Array inside an Array Formula attempt:
    {=SUM(IF(FREQUENCY(IF(B3:J3={"S","X"},COLUMN(B3:J3)),IF(B3:J3<>{"S","X"},COLUMN(B3:J3)))>0,1))}

    Thanks in advance for any ideas, suggestions and solutions!

    Excel_Noob122
    Attached Files Attached Files
    Last edited by excel_noob122; 08-22-2021 at 01:43 PM.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Count Frequency IF (this) OR (that) for events

    Hi & welcome to the board.
    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    although I'm not sure why Name6 should be 0 rather than 1

  3. #3
    Forum Contributor
    Join Date
    04-15-2013
    Location
    Singapore, Melbourne
    MS-Off Ver
    Office 365
    Posts
    167

    Re: Count Frequency IF (this) OR (that) for events

    Hi, here is an another option for your question

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You may have to press Ctrl+Shift+Enter to enter the formula.
    Row row row your boat
    Gently down the stream

  4. #4
    Registered User
    Join Date
    08-19-2021
    Location
    London
    MS-Off Ver
    O365
    Posts
    9

    Re: Count Frequency IF (this) OR (that) for events

    Thanks Fluff13! That worked and fixed all my issues!
    Name6 was an oversight on my part and should have been 1, I should have double checked!

    Thanks again really appreciate it!

    Excel_Noob122

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Count Frequency IF (this) OR (that) for events

    Glad to help & thanks for the feedback.

  6. #6
    Registered User
    Join Date
    08-19-2021
    Location
    London
    MS-Off Ver
    O365
    Posts
    9

    Re: Count Frequency IF (this) OR (that) for events

    Thanks Metoo7! I think I might be missing something with this I've tried it with "enter" and "ctrl+shift_enter" and I couldn't get it to work. Currently I'm using Fluff13's solution but thank you for looking at my post and offering your solution, much appreciated!

+ 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. Replies: 3
    Last Post: 09-06-2020, 07:18 PM
  2. frequency of individual events
    By omaram in forum Hello..Introduce yourself
    Replies: 4
    Last Post: 09-06-2020, 05:57 AM
  3. [SOLVED] Tracking Frequency of Events in Excel
    By bquade in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-21-2015, 11:21 AM
  4. [SOLVED] Count If Function To Count Frequency Of Long Numbers
    By RowanB in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-21-2013, 05:18 AM
  5. Count Frequency?
    By melnikok in forum Excel General
    Replies: 2
    Last Post: 01-21-2008, 03:11 PM
  6. frequency of events
    By rokafella in forum Excel General
    Replies: 1
    Last Post: 10-20-2005, 03:05 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