Formula to return the average of points assigned to a specific person within a specific date range

Haley C.
Haley C. ✭✭
edited 12/29/22 in Smartsheet Basics

Hi all, I have a fairly complex formula I'm tying to create, and can't manage to make it work.

Let's say my data sheet has three columns, representing a point value given to an individual on a specific date:

  • Person (Number/Text, each cell contains one name)
  • Points (Number/Text, each cell contains one number)
  • Date (Date, each cell contains one date)

In my summary sheet, I want a formula that will return the average of the points given to an individual between two dates (the first and last of a given month). The closest I've gotten is returning the average points from ALL dates using the following formula:

=AVERAGEIF({Data Sheet Range 5}, "Firstname Lastname", {Data Sheet Range 3})

where "Data Sheet Range 5" is the Person column and "Data Sheet Range 3" is the Points column.

If you feel like responding, it would be extremely helpful if you use the exact punctuation marks, syntax, and structure I'll need for the formula (with the understanding that variables like ranges will be unique to my sheets). In some responses it seems like folks use shorthand such as [DATE] which gets really confusing for a novice! Thanks in advance!