Creating SUMIFS

[School_Bus]

Ok, first I have to say thank you to everyone that has helped. I have taken way more then I have given in this community, and I really appreciate it.

Now onto the problem. I’m trying to create something similar to a sumifs function by using a Sum function with a select function, but I’m failing miserably. Here is the function that I have, but it’s not returning anything.

sum(
select(
employee ledger[employee pay],
and(
[_thisrow].[eid]=any(employee ledger[EID]),
or(
any(employee ledger[activity])=“AM Daily”,
any(employee ledger[activity])=“PM Daily”))))

[Lynn]

Hi @School_Bus
What happens in the expression tester with this expression?
Do the parts work if you break it down?

[School_Bus]

When I test the expression I don’t get any responses for any record.

I tried running just the select function, and I get no responses for any of my records. So I know it’s a problem with my select function, but I’m lost on the problem. Admittedly I am just learning how to use Select, and it’s kicking my butt.

[Steve]

What are you trying to accomplish with your SUMIF()?

[School_Bus]

I’m trying to create a sumifs for payroll. I figured it out, it was the first condition after the and function that was binding my expression. Looks like it’s up and running now.

thank you

[School_Bus]

So I tried to add a function in to filter using the date, but it really just returns everything. Can you tell me what’s wrong with my function? I suspect it’s the any() function causing me issues, but I don’t know how to get the expression to work. Thanks in advance

Sum(
select(
employee ledger[driver cost],
and(
[_thisrow].[eid]=[eid],
any(company payroll[w1start])<=[date],
any(company payroll[w1end])>=[date],
any([activity])=“Trip”
)))

[Steve]

Have you read this?

help.appsheet.com

ANY()

One arbitrary list item

All of it?

[School_Bus]

Yep, I got that it returns just one arbitrary value, but the table I’m pulling from only has a single record. I figured by using Any() I would just be left with that value from the single record. I don’t know what to do with this, and why it’s returning all instances where activity = trip. I just tried removing the any function from [activity]=“Trip” since it’s comparing a list to a value.

[School_Bus]

So when I used the ANY function it was an attempt to overcome the problem where list <> value in the select function. I think this is caused by me using a table to column function “company payroll[w1start]”

It looks like a similar question has come up in the past that you were in the conversation on, but it doesn’t look resolved. Since ANY() is just returning an arbitrary number in the list, how should I go about resolving the conflict where a value cannot be compared with a list?

I’ve linked the previous discussion thread for reference. Thanks in advance for the help

quick question about constraints. i am tryin... Questions

+Steve Coile the table TestReport contains a virtual column with this SELECT expression in it. SELECT( testdata2[Pickup Date], AND( (testdata2[Pickup Date] >= [_THISROW].[Begin Date]), (testdata2[Pickup Date] < [_THISROW].[End Date]) ) ) I get this error: Cannot compare List with DateTime in (TESTDATA2[Pickup Date] >= [_RowNumber].[Begin Date]) had similar issue with both the Count(Filter) and Sum(Select) expressions. clearly something is configured wrong, not sure where to look.

[School_Bus]

I think it’s resolved now. I am trying to use a filter table for the dashboard with this, and that means I had to reference two separate tables in the same select function. So… I created virtual columns for my w1start, w1end, etc… in this sheet that used a lookup to get the value from my filter table. Then I went back to my select function and removed that external table reference and was able to fix my search function that way. At least I think, I’ll have to try to break it tomorrow, but it looks like it works now