COUNTIFs key words

[JM45T3R5]

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

Thank you in advance!

[alansidman]

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

[bebo021999]

ONe formula in C12 then drag down:

Please Login or Register  to view this content.

[JM45T3R5]

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