ValueCount ( FilterValues ( List (Apples); "Granny Smith") ) + ValueCount ( FilterValues ( List (Colour); "Green") )
would tell you how many apples are Granny Smith OR green (cumulative). You cannot tell how many are Granny Smith AND green by looking at two independent lists.
Hmmm....that's a shame. Thanks for the clarification. Aikiko
There are, of course, other methods. It depends on what exactly you're trying to achieve. For example, you could add a calculation field in the Apples table and count/sum the results. But the best way, IMHO, is to find the matching records.
BTW, the syntax List (Apples) would work only with a repeating field - I hope that's not the case here.
Yes, List(apples) is actually List(Fruit::apples) or a variation thereof.
I would like to create a report that automatically updates itself. The report would have ~250 (50 colours x 5 types of apples) searches.
e.g. how many granny smith apples (1 of 5 apple types) are green (1 of 50 colours)?
how many golden delicious apples are yellow?
And so forth.
Perhaps would another method be to do a calc that includes a 'find' function e.g. find Apples:Granny Smith AND find 'colours:green'?
I'm not sure I understand. It seems to me that a report (produced from the Apples table) summarized by type and color would provide what you describe, in this format:
• Green: 5
* Yellow: 3
• Green: 2
* Yellow: 8
Of course, by performing a find you can control which types and colors (or combinations thereof) will appear in the report.