i will two table,
table one called student contain student details
table two is a payment_log contains payment amount, which month they paid for, date and time.
i want to create a virtual column in table student which can calculate each student’s monthly pay amount, because some of them may pay two / three times.
so a select function with two filter is my thought to this. I tired to write the formula, but it doesnt work…
Sum(select(and(payment record[paymonth] = “Mar” , payment record[payment], [name]=[_thisrow].[id])))
the error display that Cannot compare List with Text in (Payment record[Paymonth] = “Mar”)
anyone can help me?Thank you!
Solved! Go to Solution.
finally i solved it!!! thank you everyone
sum(
select(
payment record[payment],
and ( [name] = [_thisrow].[id], in([paymonth], {“Mar”}))))
Hi @HoeySho
Check out the expression in this thread for the correct syntax.
Perhaps try:
Sum(
Select(
payment_log[payment],
and(
[paymonth]=“Mar”,
in(payment_log[name],[_thisrow].[name])
)
))
Your table/column names may be different.
thank you for reply
i tried but it said
Parameter 2 of function IN is of the wrong type
try adding LIST()
in(payment_log[name],LIST([_thisrow].[name]))
finally i solved it!!! thank you everyone
sum(
select(
payment record[payment],
and ( [name] = [_thisrow].[id], in([paymonth], {“Mar”}))))
@HoeySho
Great
just try LOOKUP formula i thinks it easier to use
LOOKUP([ Column in this row for ref ] , “Tablename” , “Column in Tablename for REF” , “Column to Pull value”)
is it work if i have more than one name / month??
what i want to do is to sumup the student’s payment by specfic month
thx for reply!
Yes i use this formula for check ID , password perUser and anything to pull data
like this
OR (
[User Level] <= 10 ,
[แผนก] = “HR” ,
AND( [User Level] <= 10 , LOOKUP([Member ID] , “HR.Members” , “รหัสพนักงาน” , “แผนก”) = LOOKUP([ผู้ลา] , “HR.Members” , “รหัสพนักงาน” , “แผนก”) ) ,
AND([Action] = “SIGNIN” , [ผู้ลา]=USERSETTINGS(Member ID) ))
even more column but there secret cant show
before i understand to use REF Type
in() return the value true/false, can it also help me to filter the list?? thank you!
hi everyone,
i would like to use this sum(select) for 2 different sheets.
wondering if it is possible.
Probably.
User | Count |
---|---|
43 | |
28 | |
24 | |
21 | |
13 |