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?
Bookmarks