# combining two different formula's

1. ## 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. ## Re: combining two different formula's

welcome to the forum, MaWestra74. try this array formula:
Formula:
`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.

3. ## Re: combining two different formula's

THANKS!! It works fantastic.

Martin

There are currently 1 users browsing this thread. (0 members and 1 guests)