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 |
---|---|
60 | |
25 | |
13 | |
11 | |
6 |