# Designing a calculation field to count records

This topic is 7563 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

• Newbies

That is, I need help designing a calculation field to count records that match multiple field criteria

Example of what I am trying to do:

I have a summer camp registration database

I have records that contain, among other things, the following data for each camper:

Sex of the camper

Session attending (July or August)

If I want to design a calculation field that gives me a count of (the 4th Grade)( boys) (who will be in camp in August), how can I do that?

In other words I want to design a calculation field that will give me a count of the records that match these multiple (3) criteria

Should I stop here? Or should I say that in Excel I have used formulas containing nesting "if" statements to count the number of "records" in a spreadsheet table that match multiple criteria over a few columns. Here is a sample of such an Excel formula.

=SUM(IF((\$L\$2:\$L\$338=\$M\$350)*(\$M\$2:\$M\$338=O353),1,0))

This formula was used in the calculations related to the responses to a long and complex community survey. It counted for me the number of rows (records) in the responses where a person who had lived in the community for a certain amount of time, (\$L\$2:\$L\$338=\$M\$350) was within a specific age range (\$M\$2:\$M\$338=O353),1,0)

Can I build a similar formula into a calculation field in FileMaker that will count records that match multiple field criteria? A calculation that will count up the number of records in the database that match 4th grade, that match boys and that match campers registered for the August session so that I can see at a glance the number of 4th Grade boys who will be in camp in August?

chaversteve

##### Share on other sites

Summary fields might be able to do something similar. Use calculated fields to do the equivalent of your Excel IF statements.

However, it might be faster and easier to script a search and count the number of records found. Not as elegant though.

##### Share on other sites

For speed for the user I would use a relationship -

Create three global fields, either in the same file or another,

Create three calcs, one a concatenation of the three new globals.

One a concatenation of the Gender ,session and grade in your registration db and

A RecordIsOne field ie. a calc with "1" as the calculation and therefore fixed result.

Create a new relationship between the two calcs.

By entering your search criteria in the 3 globals you should get the record match result.

This will only give a result when all three criteria are used but you could make the gender ,session and grade concatenation a multiline field to allow matches when one or more of the search criteria is empty

##### Share on other sites

This topic is 7563 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account