11 Replies Latest reply on Mar 25, 2014 5:50 AM by philmodjunk

    Excel Count Function



      Excel Count Function


           Hi all,


           I’ve revisited this question as I’m not sure I explained it too well previously and I didn’t want to start another thread with a similar question.


           What I am trying to do is a simple Count function as in Excel, I’ve scanned the forum and this does seem to be an ongoing question with people with various solutions but I have tried these and they’re not quite doing what I need as they seem to rely on a sort order and I can’t do that as I have already got them sorted in a particular way.


           This is the layout in Excel that I’m trying to reproduce and is what I want to recreate in FileMaker



           This section is the count summary



           And this is the main count




           I’ve already created this in FMPro  and it is doing what I need it to do apart from the bit at the bottom which the feeder to the above table.


           I know that FMPro is not Excel and as such may not always be able to do what Excel does, but I thought I may be able to get the results I need with a combination of scripts, summary fields and Validation.


           Any help will be greatly appreciated even if it is to say it can’t be done at present.


           Many thanks




        • 1. Re: Excel Count Function


          • 2. Re: Excel Count Function


            • 3. Re: Excel Count Function


              • 4. Re: Excel Count Function


                • 5. Re: Excel Count Function

                       The purpose to some of your columns in the last screen shot are not clear. What is the purpose of the following columns?

                       SP, R1T/R4, R2

                       And does each row in the last screen shot represent a different record? If not, please describe what you did to produce that layout.

                       I need to be able to understand the basic set up of your tables/fields/relationships before I can suggest ways to get the results that you want.

                  • 6. Re: Excel Count Function

                         Hi Phil,


                         SP is sales person, and R1T/R4 and R2 are the roles that consultants do on a project. Which is one of the things we need to track.


                         Each row is a new record and the fields are as follows:


                         Month – Contract Number – Project – Sales Person – Year – Day 1 – Day 2 – Consultant 1 – Consultant 2 – Notes


                         Month is the sort order and I need to keep it sorted by this field

                         Contract Number is the link between all the tables I use.


                         And hopefully the rest are all self-explanatory. I know it’s not the easiest to do from the information I give but I can’t use actual screen shots as the data I have is sensitive and I can’t post it on forums.


                         Basically what I’m trying to achieve is a summary of the days that each consultants does summarised on YTD and on a month on month basis. Image2 shows the YTD and Image3 shows the month on month.






                    • 7. Re: Excel Count Function

                           And what does that one single record represent? Am I correct that you have two separate fields for identifying the consultant? Why two fields and not just one?

                           And what is the purpose of having both a day1 and a day2 field?

                      • 8. Re: Excel Count Function

                             Each record represents a project, but only the days the project took place and the consultants working on it, it's more of a tracker of the consultants number of days worked for us this is the information we are after. There are two fields for the consultant as there is two areas of expertise, likewise there are two fields for days as there are occasionally two days.


                             Hope this helps.


                             Thanks Phil

                        • 9. Re: Excel Count Function

                                    Each record represents a project, but only the days the project took place and the consultants working on it,

                               It seems that you have one record where you really need two or three. What happens if you end up needing 3 days instead of 2?

                               I'd use one table for projects where I have one record for each project and a related table for logging a single day worked on a single project by a single consultant in a single area of expertise. This table of days worked can then be linked to a table of consultants if needed.

                               That would make pulling together your summary information into a "cross tab" format such as you show much simpler.

                          • 10. Re: Excel Count Function

                                 Thanks Phil,


                                 If I do it as you suggested would it mean that users would have to fill in the information on multiple pages or would they be able to input it on just one page? I'm trying to keep it down to a minimum and on just one page. I don't mind using multiple records and tables etc if I can keep it in one front end.


                                 Could I do this as you suggest?


                                 Many thanks



                            • 11. Re: Excel Count Function

                                   This could still be data entry on one layout. That one of the purposes of using portals--to be able to work with multiple child records (days worked) from the context of a single parent record, the project. And I may have been a bit extreme in my last recommendation. You could log multiple days worked in one record, but I'd still limit each record where you log that data to a single consultant working on a single project.