Count filtered unique values where sum of values in another column meets criteria

[excelhelppleas]

Hello, hope you can help. I would like to use a formula to give the count of complete projects in [time range] where there is a total of <=1 hour spent on 'review' over the whole project - example sheet below. In this sample the desired answer is 2 as only project A and B meet the criteria.

date	project	project status	task	time (hours)
01/01/2021​	Project A	Complete	draft	0.5​
01/01/2021​	Project A	Complete	review	0.5​
02/01/2021​	Project A	Complete	report	1​
03/01/2021​	Project A	Complete	review	0.5​
04/01/2021​	Project A	Complete	publish	0.5​
05/01/2021​	Project B	Complete	draft	1​
05/01/2021​	Project B	Complete	review	1​
06/01/2021​	Project B	Complete	publish	0.5​
07/01/2021​	Project C	Complete	review	1.5​
08/01/2021​	Project D	Dropped	draft	2​
09/01/2021​	Project D	Dropped	report	0.5​
10/01/2021​	Project D	Dropped	review	0.5​
11/01/2021​	Project D	Dropped	review	1​
12/01/2021​	Project E	In Progress	draft	1​
13/01/2021​	Project E	In Progress	review	0.5​

I can count the number of projects which are complete, where time was spent on 'review' and which were undertaken between two dates using this formula but don't know how to integrate the filter / sumif for the time spent on review.

=SUM(--(LEN(UNIQUE(FILTER(B:B,(A:A>=DATE(2021,1,1))*(A:A<=DATE(2021,12,31))*(D:D="review")*(C:C="Complete"),"")))>0))

Any suggestions appreciated.

 

[Georgiboy]

As you are using the FILTER function - are you using Excel 2016?

 

[excelhelppleas]

As you are using the FILTER function - are you using Excel 2016?

Yep

 

[Georgiboy]

Strange, my version of Excel 2016 does not have the filter function, only FILTERXML.

 

[excelhelppleas]

Strange, my version of Excel 2016 does not have the filter function, only FILTERXML.

Ohhh I'm on Office 365 sorry, the product properties file it under Office16 but it's 365

 

[Georgiboy]

Think i took a long way round but the below may help:

Book1
	A	B	C	D	E	F	G	H
1	date	project	project status	task	time (hours)
2	01/01/2021	Project A	Complete	draft	0.5			2
3	02/01/2021	Project A	Complete	review	0.5
4	03/01/2021	Project A	Complete	review	0.5
5	04/01/2021	Project A	Complete	report	1
6	05/01/2021	Project Z	Complete	review	0.5
7	05/01/2021	Project A	Complete	review	0.5
8	06/01/2021	Project A	Complete	publish	0.5
9	07/01/2021	Project B	Complete	draft	1
10	08/01/2021	Project B	Complete	review	1
11	09/01/2021	Project B	Complete	publish	0.5
12	10/01/2021	Project C	Complete	review	1.5
13	11/01/2021	Project D	Dropped	draft	2
14	12/01/2021	Project D	Dropped	report	0.5
15	13/01/2021	Project D	Dropped	review	0.5
16	14/01/2021	Project D	Dropped	review	1
17	15/01/2021	Project E	In Progress	draft	1
18	16/01/2021	Project E	In Progress	review	0.5
Sheet1

Cell Formulas
Range	Formula
H2	H2	=LET(u,UNIQUE(FILTER(B2:B18,C2:C18="Complete")),s,SUMIFS(E2:E18,D2:D18,"Review",C2:C18,"Complete",B2:B18,u),COUNT(FILTER(s,s<=1)))

 

[excelhelppleas]

Hi - unfortunately this doesn't work as it returns 32 matches for <1 hour when my dataset only has 28 total projects for the year.

 

[excelhelppleas]

Hi - unfortunately this doesn't work as it returns 32 matches for <1 hour when my dataset only has 28 total projects for the year.

sorry pressed enter too soon - I added a date filter into your formula within the UNIQUE criteria similar to my initial formula, and I wonder if that is the wrong way to go about filtering for date in your solution. thanks for your help, by the way!

 

[Georgiboy]

I see, will be easier to see what is going on if we had some more data to look at, have you tried the XL2BB add in that is available on this forum?

XL2BB allows you to upload part of your workbook for us to look at what you are looking at OR a mock up of what you are looking at. I used it in post 6.

 

[excelhelppleas]

I see, will be easier to see what is going on if we had some more data to look at, have you tried the XL2BB add in that is available on this forum?

XL2BB allows you to upload part of your workbook for us to look at what you are looking at OR a mock up of what you are looking at. I used it in post 6.

Unfortunately I can't share the actual data. However I cut out most of the extra results by specifying for D:D = 'review' within the UNIQUE criteria.

=LET(
u,UNIQUE(FILTER(B:B,C:C="Complete"*(A:A>=DATE(2021,1,1))*(A:A<=DATE(2021,12,31))*(D:D="Review"))),
s,SUMIFS(E:E,D:D,"Review",C:C,"Complete",B:B,u),
COUNT(FILTER(s,s<=1)))

There are still a couple extra in my total which are being double-counted across the years as they have line entries in Dec 21 and Jan 22 (e.g.) so show up in the formula for both 2021 and 2022. Is there a way to exclude entries which have 'review' lines in a prior year?