Help with excel SUMIF solution for filemaker

[Daglas]

Hi all,

I'm trying to convert this function from excel to filemaker:

"SUMIF(J21:J36,"=8",K21:K36)"

I have 2 rows and each have 15 columns.

Columns in row 1 have names from "A1" to "A15" and are all different value numbers that user specify.

Columns in row 2 have names from "B1" to "B15" and for them user can chose value "8" or "18".

Thing that I'm trying to do is:

I'm trying to calculate sum of all values in row 1 (A1...A15) if User specified value "8" in row 2 (B1...B15) but if for example user specify for "B4", "B6", "B12" value "18" then "A4", "A6" and "A12" would be stated as false and would not be counted.

Can someone help me with this.

Thanks

Edited September 18, 2009 by Guest

[comment]

I suppose it's possible, but... Filemaker is not a spreadsheet and imposing spreadsheet ways upon it can be very inefficient - as it would be in this case.

I suggest you change your structure so that each pair of values is a record with two fields. Then the problem becomes trivial.

[Daglas]

Thanks for the hint :

That helps a loot. Will make checking buttons for each so that client can check for each value 8 or 18 and that will make it much easier. :

[comment]

What is the significance of "8" and "18" here? If you make them choose between 0 (or empty) and 1, you can then have a calculation field multiply the two fields, and a summary field to sum up the results.

Note that 0/1 can be labeled as "Yes" or "No" (or as custom labels) using Format > Number… > Format as Boolean.

[Daglas]

8 and 18 are tax rates in clients country so in the database he needs that columns in row "a" that are including tax values to be calculated for each tax rate so that total values for each tax rate are presented.

Edited September 18, 2009 by Guest
Added image with little more explenation

[comment]

I am not sure of the wider context here. If that's all that needs to be done, it could be done very simply by adding a summary field (Total of TaxValue) and summarizing the data by TaxRate. You would get a report looking like this:

TaxRate: 8%

$21

$17

$5

$8

SubTotal: $51

TaxRate: 18%

$32

$21

$12

SubTotal: $65

Grand Total: $116

[Daglas]

I just went and copied that table with 15 chars A and B fore each tax rate 8 and 18 and used and named them "A8 1" to "A8 15" and "B8 1" to "B8 15" for table that calculates 8% tax. Done the same for 18% tax table "A18 1" to "A18 15" and "B18 1" to "B18 15".

And like that it was easy to use calculations:

"B8 1" = Case ("B1" = 8 ; "B1")

and

"B18 1" = Case ("B1" = 18 ; "B1")

Rest was easy calculation of total sum :

Thanks again comment for your help

Best Regards

Daglas