Problem with sum(select(and)))

[anonymous]

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....](https://community.appsheet.com/t/need-help-nesting-two-sum-select-expressions/2090/2) 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]

[anonymous]

Perhaps try:

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

Your table/column names may be different.

[anonymous]

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

[anonymous]

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

[anonymous]

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

[anonymous]

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

thx for reply!

[anonymous]

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

[anonymous]

try adding LIST()

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

[anonymous]

finally i solved it!!! thank you everyone

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

[Lynn]

@HoeySho
Great

[anonymous]

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

[anonymous]

hi everyone,

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

wondering if it is possible.

[Steve]

Probably.