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>

 

[shyy]

Helps if you post the end result of the tab you desire.

 

[branshub]

Sorry, I guess that would make sense...
So for a result for Kellie for the period 8/1/13-8/2/13 I would want a result of $20... A count of one present ("P") times $13, plus a count of one present times $7.

 

[shyy]

So as long as either columns Has a P to sumup the class price? If so

J2 = Kellie

=SUMPRODUCT(--($A$2:$A$12=J2),--($F$2:$F$12="P")*($B$2:$B$12))+SUMPRODUCT(--($A$2:$A$12=J2),--($G$2:$G$12="P")*($B$2:$B$12))+SUMPRODUCT(--($A$2:$A$12=J2),--($H$2:$H$12="P")*($B$2:$B$12))

 

[Aladin Akyurek]

So as long as either columns Has a P to sumup the class price? If so

J2 = Kellie

=SUMPRODUCT(--($A$2:$A$12=J2),--($F$2:$F$12="P")*($B$2:$B$12))+SUMPRODUCT(--($A$2:$A$12=J2),--($G$2:$G$12="P")*($B$2:$B$12))+SUMPRODUCT(--($A$2:$A$12=J2),--($H$2:$H$12="P")*($B$2:$B$12))

Did not check whether this fits the OP question. Why this

...,--(X=x1)*(Y=y1)

pattern instead of a coherent set up like

...,--(X=x1),--(Y=y1)

or

*(X=x1)*(Y=y1)

still comes up is hard to understand.

By the way, the formula you have can be re-written as:

=SUMPRODUCT(
    --($A$2:$A$12=J2),
    --(($F$2:$F$12="P")+($G$2:$G$12="P")+($H$2:$H$12="P")>0),
    --($B$2:$B$12))

 

[branshub]

Did not check whether this fits the OP question. Why this

...,--(X=x1)*(Y=y1)

pattern instead of a coherent set up like

...,--(X=x1),--(Y=y1)

or

*(X=x1)*(Y=y1)

still comes up is hard to understand.

By the way, the formula you have can be re-written as:

=SUMPRODUCT(
    --($A$2:$A$12=J2),
    --(($F$2:$F$12="P")+($G$2:$G$12="P")+($H$2:$H$12="P")>0),
    --($B$2:$B$12))

Thanks, but there is a problem...
My data input is extremely large. What I posted, is only a sample. I have over 300 columns with dates; so I can not have a formula that looks in every column. I need a formula that will be limited by a given date range (ie I only want to look at 8/1-8/2). I believe the sumproduct may work if there was a way to limit it to the given date range.

 

[Aladin Akyurek]

Thanks, but there is a problem...
My data input is extremely large. What I posted, is only a sample. I have over 300 columns with dates; so I can not have a formula that looks in every column. I need a formula that will be limited by a given date range (ie I only want to look at 8/1-8/2). I believe the sumproduct may work if there was a way to limit it to the given date range.

I was not taking up the original problem. If the suggested formula in the right ball park, you need to specify where to look for 8/1-8/2, presumably dates.

 

[branshub]

So as long as either columns Has a P to sumup the class price? If so

J2 = Kellie

=SUMPRODUCT(--($A$2:$A$12=J2),--($F$2:$F$12="P")*($B$2:$B$12))+SUMPRODUCT(--($A$2:$A$12=J2),--($G$2:$G$12="P")*($B$2:$B$12))+SUMPRODUCT(--($A$2:$A$12=J2),--($H$2:$H$12="P")*($B$2:$B$12))

I believe the sumproduct may be a start, but there are two issues. 1) I have about 300 columns of dates; so I need a formula that I do not have to type in each column that I want it to count. 2) I need a formula that will only look in a given date range (ie I only want to look at 8/1-8/2).

 

[shyy]

Thanks Aladdin but would it make a big difference with the syntax? I thought the sumrange didn't need a ,-- in front of it.

...,--(X=x1),--(Y=y1)

vs
...,--(X=x1)*(Y=y1)

vs
...,*(X=x1)*(Y=y1)

Modify this to yours, the font in red is what you need to add to sum up a date.

8/1/13

,--(GL!$B$10:$B36>=DATE(YEAR(TODAY()),8,1))

 

[Aladin Akyurek]

Thanks Aladdin but would it make a big difference with the syntax? [...]
...,--(X=x1),--(Y=y1)

vs
...,--(X=x1)*(Y=y1)

vs
...,*(X=x1)*(Y=y1)

...

I'm assuming that the function of -- and * are known. If so, I don't think it would help tallking about consistency or coherence.

I thought the sumrange didn't need a ,-- in front of it.

Indeed, the sum range does not need any such modifier... It was unintended.