Problem with sum(select(and)))

HoeySho
Participant I

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 Solved
0 14 1,283
1 ACCEPTED SOLUTION

finally i solved it!!! thank you everyone

sum(
select(
payment record[payment],
and ( [name] = [_thisrow].[id], in([paymonth], {โ€œMarโ€}))))

View solution in original post

14 REPLIES 14

Lynn
Participant V

Hi @HoeySho
Check out the expression in this thread for the correct syntax.

Chris_Jeal
Participant V

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

ezydan
Participant I

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

Lynn
Participant V

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.

Top Labels in this Space