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:
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Task[/TD]
[TD]Elapsed Time[/TD]
[/TR]
[TR]
[TD]Complex[/TD]
[TD]383.12[/TD]
[/TR]
[TR]
[TD]Complex[/TD]
[TD]187.00[/TD]
[/TR]
[TR]
[TD]Complex[/TD]
[TD]58.00[/TD]
[/TR]
[TR]
[TD]Complex[/TD]
[TD]51.00[/TD]
[/TR]
[TR]
[TD]Complex[/TD]
[TD]45.02[/TD]
[/TR]
[TR]
[TD]Complex[/TD]
[TD]41.27[/TD]
[/TR]
[TR]
[TD]Complex[/TD]
[TD]34.00[/TD]
[/TR]
[TR]
[TD]Complex[/TD]
[TD]16.93[/TD]
[/TR]
[TR]
[TD]Corporate[/TD]
[TD]697.00[/TD]
[/TR]
[TR]
[TD]Corporate[/TD]
[TD]588.17[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Service[/TD]
[TD]Day 0[/TD]
[TD]Day 1[/TD]
[TD]Day 2[/TD]
[TD]Day 3[/TD]
[/TR]
[TR]
[TD]Complex[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Corporate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Simple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Balance[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Reference table (to convert to days)

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Day[/TD]
[TD]>[/TD]
[TD]<=[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8.01[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]16.01[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]24.01[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]32.01[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]40.01[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]48.01[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]56.01[/TD]
[TD]64[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]64.01[/TD]
[TD]72[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]72.01[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]80.01[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]88.01[/TD]
[TD]96[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]96.01[/TD]
[TD]104[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]104.001[/TD]
[TD]112[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]112.01[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]120.1[/TD]
[TD]128[/TD]
[/TR]
[TR]
[TD]16+[/TD]
[TD]>128.01[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Task[/td][td]Elapsed Time[/td][td][/td][td]Service[/td][td]Day 0[/td][td]Day 1[/td][td]Day 2[/td][td]Day 3[/td][td]Day 4[/td][td]Day 5[/td][td]Day 6[/td][td]Day 7[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Complex[/td][td]

383.12​

[/td][td][/td][td]Complex[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

2​

[/td][td]

1​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Complex[/td][td]

187​

[/td][td][/td][td]Corporate[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Complex[/td][td]

58​

[/td][td][/td][td]Simple[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Complex[/td][td]

51​

[/td][td][/td][td]Balance[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Complex[/td][td]

45.02​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]Complex[/td][td]

41.27​

[/td][td][/td][td]Day[/td][td]>[/td][td]<=[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Complex[/td][td]

34​

[/td][td][/td][td]

0​

[/td][td]

0​

[/td][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]Complex[/td][td]

16.93​

[/td][td][/td][td]

1​

[/td][td]

8.01​

[/td][td]

16​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]Corporate[/td][td]

697​

[/td][td][/td][td]

2​

[/td][td]

16.01​

[/td][td]

24​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]Corporate[/td][td]

588.17​

[/td][td][/td][td]

3​

[/td][td]

24.01​

[/td][td]

32​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td][/td][td][/td][td][/td][td]

4​

[/td][td]

32.01​

[/td][td]

40​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td][/td][td][/td][td][/td][td]

5​

[/td][td]

40.01​

[/td][td]

48​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][td][/td][td][/td][td]

6​

[/td][td]

48.01​

[/td][td]

56​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td][/td][td][/td][td][/td][td]

7​

[/td][td]

56.01​

[/td][td]

64​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td][/td][td][/td][td][/td][td]

8​

[/td][td]

64.01​

[/td][td]

72​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td][/td][td][/td][td][/td][td]

9​

[/td][td]

72.01​

[/td][td]

80​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td][/td][td][/td][td][/td][td]

10​

[/td][td]

80.01​

[/td][td]

88​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td][/td][td][/td][td][/td][td]

11​

[/td][td]

88.01​

[/td][td]

96​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td][/td][td][/td][td][/td][td]

12​

[/td][td]

96.01​

[/td][td]

104​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

[/td][td][/td][td][/td][td][/td][td]

13​

[/td][td]

104.001​

[/td][td]

112​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

22​

[/td][td][/td][td][/td][td][/td][td]

14​

[/td][td]

112.01​

[/td][td]

120​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

23​

[/td][td][/td][td][/td][td][/td][td]

15​

[/td][td]

120.1​

[/td][td]

128​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

24​

[/td][td][/td][td][/td][td][/td][td]16+[/td][td]

128.01​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

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!