+ Reply to Thread
Results 1 to 3 of 3

combining two different formula's

  1. #1
    Registered User
    Join Date
    03-12-2017
    Location
    leiden, netherlands
    MS-Off Ver
    2010
    Posts
    2

    combining two different formula's

    Hi,

    Is it possible to combine a COUNTIFS and a FREQUENCY, MATCH formula?

    I have a sheet with data, year, weeknr, departments etc... (see below an example)
    My 2 working formulas (with data on sheet "rekenen" and on sheet "Data sheet week".

    =(COUNTIFS(rekenen!C:C,'Data sheet week'!$F5,rekenen!Q:Q,'Data sheet week'!$B$2,rekenen!T:T,'Data sheet week'!$B$1))

    =SUM(IF(FREQUENCY(MATCH(rekenen!B2:B,rekenen!B2:B,0),MATCH(rekenen!B2:B,rekenen!B2:B0,0))>0,1))
    columns are A, B, C, Q, R, S and T

    2045 3/11/2017 Primary Recovery 10 March 1 2017
    2044 3/10/2017 Buffer Preparation 10 March 1 2017
    2041 3/10/2017 Primary Recovery 10 March 1 2017
    2040 3/10/2017 Media Preparation 10 March 1 2017
    2039 3/10/2017 Media Preparation 09 March 1 2017
    2024 3/8/2017 Primary Recovery 10 March 1 2017
    2023 3/8/2017 Primary Recovery 09 March 1 2016
    2022 3/8/2017 Primary Recovery 10 March 1 2017
    2021 3/10/2017 Primary Recovery 08 March 1 2016


    Now I would like to know the distinct number of dates on which Primary Recovery made an entry in 2017 in week 10. That would be 3 times.
    2045 3/11/2017 Primary Recovery 10 March 1 2017
    2041 3/10/2017 Primary Recovery 10 March 1 2017
    2024 3/8/2017 Primary Recovery 10 March 1 2017
    2022 3/8/2017 Primary Recovery 10 March 1 2017

    I can use a countifs function to count the number of times Primary recovery made an entry in 2017 in week 10 (4 times)
    And I can use a frequency, match function on the dates (3). But I don't know how to combine these 2.

    Is it even possible?

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,136

    Re: combining two different formula's

    welcome to the forum, MaWestra74. try this array formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    03-12-2017
    Location
    leiden, netherlands
    MS-Off Ver
    2010
    Posts
    2

    Re: combining two different formula's

    THANKS!! It works fantastic.

    Martin

+ 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] Combining Mid and If formula
    By Nijenhuis81 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-11-2016, 08:05 AM
  2. [SOLVED] Help in Combining Formula
    By shuriyan0924 in forum Excel General
    Replies: 13
    Last Post: 01-28-2015, 12:22 PM
  3. [SOLVED] Combining vertical cells into one and between blank rows start combining again?
    By mike_m1 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-21-2012, 12:19 PM
  4. [SOLVED] Combining two IF formula's
    By Oaks15 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-09-2012, 06:33 AM
  5. [SOLVED] Combining two formula
    By FooFighter616 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-02-2012, 07:03 AM
  6. Combining two formula
    By FooFighter616 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-02-2012, 06:41 AM
  7. Combining Formula
    By suburbanght in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-18-2007, 11:17 AM

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