Arrays with COUNTIFS and MATCH

[branshub]

I have a class 'attendance schedule' tab with multiple parents and multiple classes with a different price per class in the columns with class dates along the top row to track class attendance. On a separate tab I need to calculate what a particular parent owes for a given date range (ie weekly, bi weekly, monthly) by identifying each class a parent has an enrolled student, the price charged for that class, and counting the number of times the student is marked present in the given period, multiplying the count by the price of the class, and summing this calculation for each class the student is enrolled.

Parent	Class Price	Remaining Spots	Class Time	Day	8/1/13	8/2/13	8/3/13
Penny	$ 13.00	19	1:00 PM	Monday	A	A	E
Kellie	$ 13.00	19	1:00 PM	Tuesday	E	P	A
Robin	$ 10.00	14	2:30 PM	Friday	P	E	A
Kellie	$ 7.00	13	4:00 PM	Saturday	A	P	E
Robin	$ 7.00	13	4:00 PM	Saturday	A	A	P
Penny	$ 10.00	CLASS FULL	2:30 PM	Monday	P	A	A
Brandie	$ 10.00	CLASS FULL	2:30 PM	Monday	P	P	A
Brandie	$ 7.00	19	4:00 PM	Monday	E	P	P
Brandie	$ 7.00	19	4:00 PM	Wednesday	P	E	P
Penny	$ 5.00	19	3:30 PM	Monday	E	P	E
Brandie	$ 5.00	19	3:30 PM	Wednesday	A	E	P

<TBODY>
</TBODY>

I feel like I have not asked this question clearly, but I hope this helps. I am assuming this will use an array formula, but I cannot figure out how to do everthing I am trying to do. Any help is greatly appreciated.</SPAN></SPAN>

 

[branshub]

I appreciate the help, but I feel like I am getting no where on this issue in my workbook. So I have added a few tabs which eliminates my issue of needing to multiply my count and my class rate.
I have new question that is related to original post. I was not sure if proper ettiquete was to post in this thread or create a new one.

What I am needing to do now is create a formula that identifies a single row to sum, based on a match in the far left column, and only sums based a a given date range. Below is a section of my data that I am needing to sum (sorry, I can't figure out how to get a screenshot posted to make it more apparent). On a second tab set up for Penny, I have beginning date of 1/1/13 and an end date of 1/3/13. I am trying to get a formula that identifies the row for Penny and can automatically sum only the values in the columns 1/1/13 thru 1/3/13. My desired result would be 61 (ie 23+28+10). This workbook is used for billing. What I am doing here, is attempting to create a calculation for what is owed by Penny for a given date.

Parent	1/1/13	1/2/13	1/3/13	1/4/13	1/5/13
Kellie	7	20	13	7	13
Robin	17	7	17	10	17
Brandie	22	17	29	24	24
Penny	23	28	10	28	10

<TBODY>
</TBODY><COLGROUP><COL><COL span=5></COLGROUP>

 

[Aladin Akyurek]

I appreciate the help, but I feel like I am getting no where on this issue in my workbook. So I have added a few tabs which eliminates my issue of needing to multiply my count and my class rate.
I have new question that is related to original post. I was not sure if proper ettiquete was to post in this thread or create a new one.

What I am needing to do now is create a formula that identifies a single row to sum, based on a match in the far left column, and only sums based a a given date range. Below is a section of my data that I am needing to sum (sorry, I can't figure out how to get a screenshot posted to make it more apparent). On a second tab set up for Penny, I have beginning date of 1/1/13 and an end date of 1/3/13. I am trying to get a formula that identifies the row for Penny and can automatically sum only the values in the columns 1/1/13 thru 1/3/13. My desired result would be 61 (ie 23+28+10). This workbook is used for billing. What I am doing here, is attempting to create a calculation for what is owed by Penny for a given date.

Parent	1/1/13	1/2/13	1/3/13	1/4/13	1/5/13
Kellie	7	20	13	7	13
Robin	17	7	17	10	17
Brandie	22	17	29	24	24
Penny	23	28	10	28	10

<tbody>
</tbody>

Let Sheet1, A:F, house the data.

Penny, A1, houses the sheet name Penny, A2 1/1/13, and B2 1/3/13.

Either...

C2, control+shift+enter, not just enter:

=SUM(IF($B$1:$F$1>=$A2,IF($B$1:$F$1<=$B2,
  INDEX(Sheet1!$A$3:$F$6,MATCH($A$1,Sheet1!$A$3:$A$6,0),0))))

 

[branshub]

Let Sheet1, A:F, house the data.

Penny, A1, houses the sheet name Penny, A2 1/1/13, and B2 1/3/13.

Either...

C2, control+shift+enter, not just enter:

=SUM(IF($B$1:$F$1>=$A2,IF($B$1:$F$1<=$B2,
  INDEX(Sheet1!$A$3:$F$6,MATCH($A$1,Sheet1!$A$3:$A$6,0),0))))

THANK YOU!!!! I've been working on this workbook for three weeks, and I could not figure this out. Your formula gave me exactly what I needed.

 

[Aladin Akyurek]

THANK YOU!!!! I've been working on this workbook for three weeks, and I could not figure this out. Your formula gave me exactly what I needed.

You are welcome. Thanks for providing feedback.