how to sum A sheet's one column if his one field equals some condition
This is a messy question i agree... let me rephrase it:
I got 2 sheets: Sheet A , Sheet B
In Sheet A there is one field called MONEY(all kinds of money record) and one field called USER (somebody's nickname like P or M or E)
What I'd like to archive is in Sheet B's field named NOTE, it can automatically look up in SheetA's MONEY and sum every data if it's field USER is P.
Basically it can be done by one link field, one lookup field and a roll up field, but need to update manually if anyone add anything in the data source sheet (SheetA)
I guess my question is that is there any way to do that automatically like in excel? Which can be done easily by using the count if() and sum() functions.
Accepted Solutions
The short answer is yes. The longer answer is yes, but it's different than it is in Excel. The much longer answer is below.
- Change table B to a users table. Put the initials of the users in the name column
- In your table A, change the user field from a single line text field to a field that links to the users table. Make sure that you have it set to **not** accept multiple records. Only one user per record, just like you have it now.
- Add a rollup field to your users table that sums the money field from table A. You can add a lookup as well if that makes your heart sing.
The short answer is yes. The longer answer is yes, but it's different than it is in Excel. The much longer answer is below.
- Change table B to a users table. Put the initials of the users in the name column
- In your table A, change the user field from a single line text field to a field that links to the users table. Make sure that you have it set to **not** accept multiple records. Only one user per record, just like you have it now.
- Add a rollup field to your users table that sums the money field from table A. You can add a lookup as well if that makes your heart sing.
