Help needed! Google Sheets count dates from last month?

[hristos]

Dear Everyone!
I want to ask your help. I have read all about this forum, but the solutions i've found, not work for me in google sheets.
My wife leads a Development Centre that helps little children to develop motor skills.
At the beginning of a new month, she has to send a report to the parents, about how many sessions did their child take. Because of they always use the google calendar it tooks 1-2 whole day to check every entry on the calendar one by one, and i found a solution for her. There is an automation that is fetching google calendar to google sheets.
We have 2 row, A is date of lesson, B is child name. When it is a replacement lesson, because of a justified absence, B column is contans "refund" word.
I need a formula that always show how many sessions did XY child take last month, and "refund" lessons not counted. It's important, that the list that contains all lessons is a different workbook, and each children have an own workbook.
Sheets look like this now:

Main sheet with all lessons
Lesson date	Name
2021.05.29.	Tony
2021.05.30.	Eric
2021.05.31.	Barbara refund
2021.06.01.	Richie
2021.06.02.	Andy
2021.06.03.	Daniel
2021.06.04.	Tony
2021.06.05.	Eric
2021.06.06.	Barbara
2021.06.07.	Richie
2021.06.08.	Andy
2021.06.09.	Daniel
2021.06.10.	Tony
2021.06.11.	Eric
2021.06.12.	Barbara
2021.06.13.	Richie
2021.06.14.	Andy
2021.06.15.	Daniel
2021.06.16.	Tony
2021.06.17.	Eric
2021.06.18.	Barbara
2021.06.19.	Richie
2021.06.20.	Andy
2021.06.21.	Daniel
2021.06.22.	Andy
2021.06.23.	Daniel

Barbara lessons from last month exluded refund lessons
i need the formula pasted to here	i write lesson price here
total payable:	A2XB2

I know its sounds a little too difficult, but i hope you guys can give me a solution.
Many thanks,
Hristos

 

[Crystalyzer]

you have two options

1) You can create a second sheet as illustrated below. Then your individual student worksheets can pull from column B on that sheet.

The formulas are as follows
B2:B8 = =COUNTIFS(Sheet1!$B$2:$B$27,"="&A2)
C2:C8 = =COUNTIFS(Sheet1!$B$2:$B$27,"="&A2& " Refund")
D2:D8 = =B2+C2

2) simply copy this formula =COUNTIFS(Sheet1!$B$2:$B$27,"="&A2)into the individual student worksheets changing A2 to the students name in "" (i.e. "Barbara")

 

[hristos]

Thank you, but i want the formula only count the lessons from last month, and the dates are in the A column. How can i add this parameter to this formula? thank you

 

[Crystalyzer]

Thank you, but i want the formula only count the lessons from last month, and the dates are in the A column. How can i add this parameter to this formula? thank you

Sorry, missed that requirement. Your dates don't appear to be formatted as I would expect them to be so I changed them to dates in my data. This may not work if the data is not a true date in which case you will have to correct the data for these formulas to work.

I inserted a row at the top of the range for the Month End Date for which you want to return the data. The formulas are below:

=COUNTIFS(Sheet1!$B$2:$B$27,"="&A3, Sheet1!$A$2:$A$27,"<="&$B$1,Sheet1!$A$2:$A$27,">="&DATE(YEAR($B$1),MONTH($B$1),1))

=COUNTIFS(Sheet1!$B$2:$B$27,"="&A3& " Refund", Sheet1!$A$2:$A$27,"<="&$B$1,Sheet1!$A$2:$A$27,">="&DATE(YEAR($B$1),MONTH($B$1),1))