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

 

[JenniferMurphy]

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.

No problem. If we go back and count the number of times you messed up and the number of times I did, I am winning in a landslide. And I won't even claim that the election was stolen. Thanks for all your help on so many questions.

 

[Alex Blakenburg]

Not using Countifs.
That needs you to do something to the range parameter which converts it to an array and the CountIfs group of functions only allow a "Range" for that parameter.

=countifs( criteria_range, criteria,...)

 

[Alex Blakenburg]

PS: Thank YOU

 

[JenniferMurphy]

Not using Countifs.
That needs you to do something to the range parameter which converts it to an array and the CountIfs group of functions only allow a "Range" for that parameter.

=countifs( criteria_range, criteria,...)

Yeah, I tried =COUNTIFS(Table1[Date],">="&FirstDay,Table1[Systolic],ISNUMBER(Table1[Systolic]) but it returned an array.

 

[FormR]

If my data could be any number, is there an expression that will work for that?

You could test that the number is smaller than the biggest number you can have in Excel - i.e.

=COUNTIFS(Table1[Date],">="&FirstDay,Table1[Systolic],"<=9.99999999999999E+307")

 

[JenniferMurphy]

You could test that the number is smaller than the biggest number you can have in Excel - i.e.

=COUNTIFS(Table1[Date],">="&FirstDay,Table1[Systolic],"<=9.99999999999999E+307")

Clever

 

[JenniferMurphy]

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

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

I am changing the answer to this thread to this solution. I wanted to use CountIfs because it clearly (for me) showed the two criteria. The SumProduct solution is less obvious to me.

Sanjeev, thanks for your help. I will keep that solution in mind, too.