Count only if 2 conditions are met

[DJ Winzlow]

Hi everyone.

This is my first time posting. I am trying to write a formula to only count if 2 conditions are met.
In Sheet1 I have a table which has 'task names' in column A and in column B 'elapsed time' (how long the task has been opened for in hours).

In Sheet2 I have a table which I am trying to write the formulas to which has 'Task name' in column A and then columns with number of days outstanding (column B '0 days', column C '1 Day' etc..- basically an aging profile of the tasks in days (not hours). I have also added a reference table in sheet2 to as the rule to convert hours to days i.e. 0 - <=8 equals 1 day, >8.01 - <=16 equals 2 days.

Here is what I am trying to achieve.

Condition 1 - The task name in Sheet2 cell A2 must match the task name in Sheet1 column A (only count if the task name matches) THEN
Condition 2 - Now in Sheet1 column B (Elapsed time) I need it to count only the times within the criteria so for day 1 I need it to only return a count of how many are within the required range.

I can write the formulas for each individually but cannot combine the two to achieve the final outcome.

Name Match: =COUNTIFS('Aging Profile Data'!A4:A1661,'Aging Profile Dashboard'!A2)

Count how may in range - =COUNTIF('Aging Profile Data'!A2:A91,'Aging Profile Dashboard'!Q13(Reference Table)-COUNTIF('Aging Profile Data'!A4:A91,'Aging Profile Dashboard'!P13(Reference Table))

Any help would be much appreciated!!???

Sheet1:

Task	Elapsed Time
Complex	383.12
Complex	187.00
Complex	58.00
Complex	51.00
Complex	45.02
Complex	41.27
Complex	34.00
Complex	16.93
Corporate	697.00
Corporate	588.17

<tbody>
</tbody>

Sheet2:

Service	Day 0	Day 1	Day 2	Day 3
Complex
Corporate
Simple
Balance

<tbody>
</tbody>

Reference table (to convert to days)

Day	>	<=
0	0	8
1	8.01	16
2	16.01	24
3	24.01	32
4	32.01	40
5	40.01	48
6	48.01	56
7	56.01	64
8	64.01	72
9	72.01	80
10	80.01	88
11	88.01	96
12	96.01	104
13	104.001	112
14	112.01	120
15	120.1	128
16+	>128.01

<tbody>
</tbody>

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​
1​	Task	Elapsed Time		Service	Day 0	Day 1	Day 2	Day 3	Day 4	Day 5	Day 6	Day 7
2​	Complex	383.12​		Complex	0​	0​	0​	1​	0​	1​	2​	1​
3​	Complex	187​		Corporate	0​	0​	0​	0​	0​	0​	0​	0​
4​	Complex	58​		Simple	0​	0​	0​	0​	0​	0​	0​	0​
5​	Complex	51​		Balance	0​	0​	0​	0​	0​	0​	0​	0​
6​	Complex	45.02​
7​	Complex	41.27​		Day	>	<=
8​	Complex	34​		0​	0​	8​
9​	Complex	16.93​		1​	8.01​	16​
10​	Corporate	697​		2​	16.01​	24​
11​	Corporate	588.17​		3​	24.01​	32​
12​				4​	32.01​	40​
13​				5​	40.01​	48​
14​				6​	48.01​	56​
15​				7​	56.01​	64​
16​				8​	64.01​	72​
17​				9​	72.01​	80​
18​				10​	80.01​	88​
19​				11​	88.01​	96​
20​				12​	96.01​	104​
21​				13​	104.001​	112​
22​				14​	112.01​	120​
23​				15​	120.1​	128​
24​				16+	128.01​

Sheet: Sheet2

This seems to work. Array formula in cell E2:

=INDEX(FREQUENCY(IF($D2=$A$2:$A$11,$B$2:$B$11,""),$E$8:$E$23),COLUMNS($A$1:A1))

How to enter an array formula.
1. Paste formula to cell E2.
2. Press and hold CTRL + SHIFT
3. Press Enter

 

[jorismoerings]

Hi,

try this:

Book1
	A	B	C	D	E	R
1	Service	Day 0	Day 1	Day 2	Day 3	Day 16 +
2	Complex	0	0	1	0	2
3	Corporate	0	0	0	0	2
4	Simple	0	0	0	0	0
Sheet2

Cell Formulas
Range	Formula
B2	=COUNTIFS(Sheet1!$A$2:$A$11,Sheet2!$A2,Sheet1!$B$2:$B$11,"<="&8*(1+VALUE(RIGHT(Sheet2!B$1,2))),Sheet1!$B$2:$B$11,">"&8*(VALUE(RIGHT(Sheet2!B$1,2))))
R2	=COUNTIFS(Sheet1!$A$2:$A$11,Sheet2!$A2,Sheet1!$B$2:$B$11,">="&8*16)

@oscar, Your solution will work correctly if you change the array used for the determination of number of days.
=INDEX(FREQUENCY(IF($D2=$A$2:$A$11,$B$2:$B$11;""),$F$8:$F$23),COLUMNS($A$1:A1))

 

[DJ Winzlow]

Thank you @jorismoerings I used your correction to @Oscar And it worked forme. thanks for taking the time to respond!

 

[DJ Winzlow]

Thanks @oscar. I used the correction from @jorismoerings to your formula and it worked, Thank you for helping out!