Problem with sum(select(and)))

[HoeySho]

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!

[Lynn]

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

Need help nesting two SUM(Select expressions.... Questions

How about… SUM(SELECT(FG Inventory[Calculated Balance], AND([Flavour]=[_THISROW].[Flavour],[Country]=[_THISROW].[Country],[Packaging]=[_THISROW].[Packaging]))) + SUM(SELECT(WH2 Inv[Calculated Balance], AND([Flavour]=[_THISROW].[Flavour],[Country]=[_THISROW].[Country],[Packaging]=[_THISROW].[Packaging]

[Chris_Jeal]

Perhaps try:

Sum(
Select(
payment_log[payment],
and(
[paymonth]=“Mar”,
in(payment_log[name],[_thisrow].[name])
)
))

Your table/column names may be different.

[HoeySho]

Need help nesting two SUM(Select expressions....

(SELECT(FG Inventory[Calculated Balance], AND([Flavour]=[_THISROW].[Flavour],[Country]=[_THISROW].[Country],[Packaging]=[_THISROW].[Packaging]))

thank you for reply

Chris_Jeal:

Sum(
Select(
payment_log[payment],
and(
[paymonth]=“Mar”,
in(payment_log[name],[_thisrow].[name])
)
))

i tried but it said

Parameter 2 of function IN is of the wrong type

[Chris_Jeal]

try adding LIST()

in(payment_log[name],LIST([_thisrow].[name]))

[HoeySho · Accepted solution]

finally i solved it!!! thank you everyone

sum(
select(
payment record[payment],
and ( [name] = [_thisrow].[id], in([paymonth], {“Mar”}))))

[Lynn]

@HoeySho
Great

[ezydan]

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”)

[HoeySho]

is it work if i have more than one name / month??

[HoeySho]

what i want to do is to sumup the student’s payment by specfic month

thx for reply!

[ezydan]

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]

help.appsheet.com

IN()

Is item in list?

[HoeySho]

in() return the value true/false, can it also help me to filter the list?? thank you!

[Joseph_Lee]

hi everyone,

i would like to use this sum(select) for 2 different sheets.

wondering if it is possible.

[Steve]

Probably.