COUNTIF with variable dynamic range

[SAM SAM]

hi everyone!

i want to count how many times a student attended a class in his enrolled month. Pls refer to "Attendance" worksheet. The data is found here.

I want to put the formula in "PAYMENT" worksheet, in cell P2 (highlighted in yellow). and just drag the formula down.

for the countif variable range --> Each student has different enrolled period. The period can be seen in Payment worksheet, in columns J and O. (highlighted in pink)

Another question is:
Pls see Class Summary worksheet. I want to copy the values of "Attendance" worksheet.
I have put the formulas in Class Summary worksheet. The pattern is the column skips one column. Example, B3,D3,F3. But when i drag the formulas, the formula doesnt adjust accordingly. I have filled up the columns up until the last column. So im looking for a formula that i can use to do this.

Thank you in advance. Its been a long time since i last programmed in excel. I have been reviewing for the last few days. But i have a deadline to catch.

I love this forum. This has revived my love for excel.

 

[Luke M]

Hi SAM SAM, and welcome to the forum!

First, I would strongly advise re-thinking how your raw data gets input. With the information scattered all over the sheet, it makes subsequent analysis very hard. XL works best when you use a flat file type format, using tables and column headers. I've tried to show that layout in the new sheet of attached.

However, we can't always improve things, so I've also created the two formulas you requested.

 

[SAM SAM]

Hi Luke!

Wow! im so amazed!!! Your suggestion is really good. I have never used pivot table though. Do u mind to explain a bit? Im using Excel 2002. I would like to do this again for practice. I would also like to do pivot table per month, each in a separate worksheet.

Regarding inputting data, for ex. for June1, is it necessary that i input the students per class consecutively? Or is it okay to jumble the data?
like this:
June 1 CONTEMPORARY SANTIAGO
JUNE 1 CREATIVE MOVEMENT UY
JUNE 1 CONTEMPORARY GUIDO
is this okay?

your suggestion really works better. Because with the way i input data, the last column is October 4. So if a students' class period is October 3 to November 2, then November 2 will be on another sheet. Then i have another problem to solve.

With your suggestion, i can have data until Dec 31, 2016, right? i mean, one worksheet would fit this much data. (max of 20 students per class).

Thank you very very much! You are a lifesaver!!

regards,
sam

 

[SAM SAM]

hi again Luke!

i found the pivot table tutorial here. I will study this first. Hope i can get it perfectly!!!

Thanks again!
sam

 

[Luke M]

Glad you found the tutorial.

To answer your question, no, you don't have to enter data consecutively. It could be all mixed up, and the PivotTable will sort it out for you. One more benefit. There's no real limit to how much data you put in. You could even have the PivotTable filter on date, to show all the data, just 2016, just 2017, or 2015 and 2016, etc.

 

[SAM SAM]

Hi Luke!!
i think ive got PivotTable! im so happy! This is a very powerful tool.

Just have a quick question. When i get the grand total in a pivot table, beside it, i want to get 65% of the grand total. So i type =Cell * 0.65. but as i do this, the formula shows:
=GETPIVOTDATA("SALES",A3,"CLASS","BASIC JAZZ")*0.65
and i tried to drag the formula, it is fixed. How do i do this?

i have another problem:
The Titanium Package, which is Unlimited sessions (60 sessions in 1 month), originally is P150/session. But we have a change in this.

If a student attends only 30 sessions in 1 month, one session would be
9000/30 = 300/session

So, the Titanium session depends on the no. of sessions a student attends in 1 month. A student's enrolled month varies too. We have to wait for the student to finish his monthly classes to determine how much per session.

What do u suggest i do to incorporate this? It becomes so complicated. I have uploaded the updated file for your reference.

Thank you in advance. Really really appreciate it!

regards,
sam sam

 

[Luke M]

For the first, XL is "trying" to be helpful and creating a PivotData function. Since you don't need that, I would change the formula in F5 to be:
=$E5 * F$4

Then you can copy it down and to the right. Just be careful having the formulas right next to the PivotTable...if you get more dates in the table, the PT will try to expand and will override the formulas.

I don't fully understand your business model, so I'm somewhat guessing here as to how to answer question about pricing. You could have another data field in the PT, doing a basic 'COUNT' to record how many times someone attends. Perhaps that would help you out? Might need to do some sort of IF statement, checking how many sessions they went to?

 

[SAM SAM]

hello Luke!!

Thank you so much for your help!

I tried this =$E5 * F$4 in the pivot table, but when i drag the formula, the value is all the same like the value in F5. Am i doing it correctly?

Is it possible to have this - like sum and count in one pivot table?

Thank you once again!

regards,
sam

 

[Luke M]

Hi Sam,

See attached. I corrected the formula, so you can see how it should work. I also added in the Sales field a 2nd time to PivotTable data area, and set it to do a count. Is that what you were wanting?