COUNTIFs key words

1. COUNTIFs key words

Hello

I am trying to create a formula that counts the number of 'TRUE' cells in a particular column, based on certain criteria.

Here, I have created some dummy portfolios. Each of the three individuals (Me, John, Simon) has a column that shows the number of breaches ('TRUE').

In the table on the left (yellow highlight) I am trying to calculate how many 'TRUEs' there are in the column, between a certain time period (cells C5:C6).

The idea would be that this table would update, whenever you change the dates. i.e. how many breaches in the last year, or last week, etc.

Attachment 688132

I have attached the dummy excel file  Register To Reply

2. Re: COUNTIFs key words

In C12 =COUNTIFS(\$H\$12:\$H\$39,"FALSE",\$E\$12:\$E\$39,">="&C6,\$E\$12:\$E\$39,"<="&C5) and use the same concept for C13 and C14

EDIT: Noticed I had the formula backwords. I tested for FALSE. Just change formula to "TRUE"

Excel 2016 (Windows) 32 bit
A
B
C
D
1
2
Goal = count the number of breaches in a given time period
3
Breach = 'TRUE' (Columns K, N and Q)
4
5
Start
=TODAY()-2
6
End Date
6/10/2020
7
8
9
10
Fund Name
Number of breaches
11
12
My Portfolio
=COUNTIFS(\$H\$12:\$H\$39,"FALSE",\$E\$12:\$E\$39,">="&C6,\$E\$12:\$E\$39,"<="&C5)
13
John's Portfolio
=COUNTIFS(\$K\$12:\$K\$39,"FALSE",\$E\$12:\$E\$39,">="&C6,\$I\$12:\$I\$39,"<="&C5)
14
Simon's Portfolio
=COUNTIFS(\$N\$12:\$N\$39,"FALSE",\$E\$12:\$E\$39,">="&C6,\$E\$12:\$E\$39,"<="&C5)
15
 Sheet: Sheet1

Excel 2016 (Windows) 32 bit
A
B
C
D
1
2
Goal = count the number of breaches in a given time period
3
Breach = 'TRUE' (Columns K, N and Q)
4
5
Start
7/22/2020
6
End Date
6/10/2020
7
8
9
10
Fund Name
Number of breaches
11
12
My Portfolio
23
13
John's Portfolio
22
14
Simon's Portfolio
22
15
 Sheet: Sheet1  Register To Reply

3. Re: COUNTIFs key words

ONe formula in C12 then drag down:   Register To Reply

4. Re: COUNTIFs key words

Hello alansidman and bebo021999

Both of these worked well thank you!!!

bebo021999, do you know if it would possible to exchange the CHOOSE MATCH function, with an INDEX MATCH function in your formula?

thanks  Register To Reply