How to count the number of numbers in a given period?

[JenniferMurphy]

I have a spreadsheet for recording various medical metrics requested by my doctor (blood pressure, weight, etc). I have expressions for calculating the max (using MaxIfs), min (using MinIfs), and average (using AverageIfs) of a period of time. But I am having trouble coming up an expression for the number of readings. I tried to use CountIfs, but could not come up with an expression for the criteria. I also tried SumProduct as in the mini-sheet below, but that is also not working.

I would appreciate any help.

Book1
	B	C	D
4
5		Date	Systolic
6		8/20/24	102
7		8/27/24
8		8/30/24	97
9		9/15/24	113
10		9/22/24
11		9/30/24	114
12		10/05/24
13		10/15/24
14		10/20/24	98
15	Number of days	30
16	First day	09/20/24
17		Number of readings	0
18		Maximum	114
19		Average	106
20		Minimum	98
Sheet1

Cell Formulas
Range	Formula
C16	C16	=TODAY()-NumDays
D17	D17	=SUMPRODUCT((Table1[Date]>=(TODAY()-NumDays)), ISNUMBER(Table1[Systolic]))
D18	D18	=MAXIFS(Table1[Systolic],Table1[Date],">=" & FirstDay)
D19	D19	=AVERAGEIFS(Table1[Systolic],Table1[Date],">=" & FirstDay)
D20	D20	=MINIFS(Table1[Systolic],Table1[Date],">=" & FirstDay)

Named Ranges
Name	Refers To	Cells
FirstDay	=Sheet1!$C$16	D18:D20
NumDays	=Sheet1!$C$15	D17, C16

 

[Sanjeev1976]

Try using the below formula :

=SUMPRODUCT(--(Table1[Date]>=B13),ISNUMBER(Table1[Systolic])*1)

 

[JenniferMurphy]

That works. Thanks.

So is there no way to do it using CountIfs?

 

[Sanjeev1976]

You can use the below for Countifs :

=COUNTIFS(Table1[Date],">"&B13,Table1[Systolic],"<>"&"")

 

[JenniferMurphy]

You can use the below for Countifs :

=COUNTIFS(Table1[Date],">"&B13,Table1[Systolic],"<>"&"")

That almost works. The problem is that it counts all non-blank cells. That includes cells with letters or blanks.

Book1
	B	C	D
5		Date	Systolic
6		8/20/24	102
7		8/27/24
8		8/30/24	97
9		9/15/24	113
10		9/22/24
11		9/30/24	114
12		10/05/24	b
13		10/15/24
14		10/20/24	98
15
16
17	Number of days	52
18	First day	08/30/24
19		Number of readings	4
20		Number of readings	6
21		Maximum	114
22		Average	106
23		Minimum	97
Sheet1

Cell Formulas
Range	Formula
C18	C18	=TODAY()-NumDays
D19	D19	=SUMPRODUCT(--(Table1[Date]>=FirstDay), ISNUMBER(Table1[Systolic])*1)
D20	D20	=COUNTIFS(Table1[Date],">="&FirstDay,Table1[Systolic],"<>"&"")
D21	D21	=MAXIFS(Table1[Systolic],Table1[Date],">=" & FirstDay)
D22	D22	=AVERAGEIFS(Table1[Systolic],Table1[Date],">=" & FirstDay)
D23	D23	=MINIFS(Table1[Systolic],Table1[Date],">=" & FirstDay)

Named Ranges
Name	Refers To	Cells
FirstDay	=Sheet1!$C$18	D19:D23
NumDays	=Sheet1!$C$17	C18

 

[Alex Blakenburg]

So is there no way to do it using CountIfs?

an expression for the number of readings

Are you perhaps over thinking this, that is exactly what =COUNT does

If you really have to use COUNTIFS then this seems to work fine:

=COUNTIFS(Table1[Systolic],"<300")

 

[FormR]

Hi, assuming it can never be zero or less than zero, you could also try:

=COUNTIFS(Table1[Date],">="&FirstDay,Table1[Systolic],">0")

 

[JenniferMurphy]

Are you perhaps over thinking this,

Me? Naw? Never. At least I don't think so. Wait! Let me think about that...... I'll get back to you on that.

that is exactly what =COUNT does

I didn't think count would work because I only want to count the numbers in the last N days.

If you really have to use COUNTIFS then this seems to work fine:

=COUNTIFS(Table1[Systolic],"<300")

Huh? Where did the 300 come from?

 

[Alex Blakenburg]

Oops my bad I didn't pick up on the date criteria.
I figured blood pressure wouldn't go over 300 but any number seems to force it to only pick up numbers.
I thought when I tried >0 it didn't work but that was my mistake and that works as well.

 

[JenniferMurphy]

Hi, assuming it can never be zero or less than zero, you could also try:

=COUNTIFS(Table1[Date],">="&FirstDay,Table1[Systolic],">0")

Yes! That's what I was looking for. My data can never be zero or negative, so that works.

I was looking for a way to use IsNumber(). If my data could be any number, is there an expression that will work for that?