Need help with complex formula

[Esch]

I teach drawing classes to kids. For a while I've been using Open Office Calc as a quasi database to display these data points in pivot tables. Up to this point everything has worked well, but I'm trying to create a new set of stats for my classes and have run into issues beyond my skill level with formulas. In these stats, I have 7 data points I collect. (see screenshot 2125 attached.)

Every time a parent signs up for a class, I will add their info to this data list. The first problem that I managed to fix was "new customers" versus "returning customers" and number of classes taken. I created a new Sheet (screenshot 2126) and put pivot tables in it that tallied up how many classes each parent has signed up for referencing the PARENT NAME column from the main sheet. I then have a section on the 2125 screenshot (far right) where I display the number of classes taken using a formula that does less than/greater than from those pivot tables.

The issue I can't resolve is everything else. I also want to break down things by age, gender and the State they live in, but I want it to only count each parent once. If a parent signs up for five classes, the spreadsheet currently "as is" will think it's five different kids with the same age/gender/State, and in the end my stats will be inaccurately inflated because of all the duplication.

I'm looking for a formula that takes the PARENT NAME column, and then only tallies up each unique instance for age/gender/State so it's only counting it one time regardless of how many entries. It doesn't appear I can do it with Pivot Tables specifically but I may be wrong on that?

I can't leave these cells blank for additional classes a parent might take because I also plan to break the stats down by year, and I have some kids I've been teaching for more than one year. That would mean it would only show one instance of them in the first year they took a class, but everything would be blank in those cells for the next year, and then they wouldn't be counted at all for that year.

Hope that makes sense, if I need to reword it please ask.
Any help you can give would be greatly appreciated. Thank you!!!
E

[FJCC]

I assume you want to calculate statistics on the age, gender, and state of the children. Also, tracking parents might run into difficulty if a parent has more than one child in your classes. Might it make more sense to have a separate table of children, listing each once with the age etc. You could then fill in much of the information in the table of your second screen shot using vlookup functions. You could add a column to that table identifying the student, probably with an ID number, and the age, gender, state and parent would fill in automatically. Does that make sense?

A real database is a better tool for this sort of thing, though I suspect you know that.

[Esch]

Thanks for the reply! Yeah, ideally I would love to have a real database run all of this but I am not a programmer and can't afford to hire one, unfortunately. Everything I've done with this up to this point has been one giant hack job to get things to do what they aren't programmed to do, lol. In regards to multiple kids from the same parent, that does happen sometimes and the plan was to list each child separately but still have the parent name attached to them. Of course that may cause problems when sorting.

So if I understand what you're saying, you are suggesting to use the child's full name as the filter and not the parent's full name, is that correct? I would then add a new column with the child's name but leave the rest of the information the same, then use vlookup to do the sorting. Am I getting that right?

E

[John_Ha]

A real database is a better tool for this sort of thing, though I suspect you know that.

As always, do a web search to see if what you want is already available. Searching with database managing class activities students parents found INTRODUCTION TO THE ACCESS DATABASE - Keeping student records which is about Microsoft Access database, but seems very relevant.

The most important thing to remember is that each piece of data should be entered in only one place. If a piece of data is entered in two places it is only a matter of time before they get out of step and have different values no matter how hard you try to keep them the same.

Base isn't the easiest thing to use, nor the best documented, so you may find investing in Access to be worthwhile. LO Base has better documentation than AOO Base.

However, once a spreadsheet gets complicated it is all too easy to corrupt it accidentally while working with it.

A data base works on a different concept: First, pour in the data and store it. Second, analyse the stored data and produce reports. Data entry and analysis are thus separate activities and one cannot corrupt the other. Database design is critical and is done on paper before sitting at the PC.

In answer to your question, I would give everyone a unique identification number as too many people have the same names.

[Villeroy]

I'm looking for a formula that takes the PARENT NAME column, and then only tallies up each unique instance for age/gender/State so it's only counting it one time regardless of how many entries. It doesn't appear I can do it with Pivot Tables specifically but I may be wrong on that?

Drag age, gender and state to row fields and count any consecutive field in a data field.