Sumif condition - Appsheet Style

I am trying to create a formula in a virtual column for Vacation hours used, vacation hours remaining.

My main Employee sheet has their vacation hour allocation for the year. I have a time off able that records vacation hours requested and based on the code, allocates hours.

So basically I need to sum records based on the employees email and subtract from the allocated time.

In short, if John has used 16 hours and gets 80 hours, it would display 16 used and 64 remain,

Thinking I need virtual columns in the employee table but then drawing a blank.

Click through this link:

1 Like

Thank you @Steve

I am trying to further limit by request type. In this instance, i only want to see how many vacation hours were used. I am guessing my argument needs to be modified a little. Can you help me please

SUM(
SELECT(
Time Off[Hours Used],
AND(
([Name] = [_THISROW].[Name]),
([Date] <= [_THISROW].[Date])

(“Vacation”) = [THISROW].[Request Type]

)
)
)

What isn’t working? Is there an error? If so, can you share a screenshot of it?

@Steve

It’s essentially grabbing the same hours vacation time, sick time, and floating.

The formula works but somehow not distinguishing between the 3 time types

1 Like

@Steve

The app

12 hours used across the board

Vacation = 20
Sick = 8

1 Like

Please provide a screenshot of the full expression used. I’d prefer a screenshot to avoid any typos.

@Steve

1 Like

@Steve

Made an adjustment. Noticed i had a code issue with () and … This is corrected but now not calculating anything

1 Like

@Steve

I got to this formula to lookup vacation time remaining. It does capture the number of days used but is capturing everything, not just vacation

Syntax???

1 Like

image

Could you please try the following expression

SUM(SELECT( Time Off[Hours Used], AND([Name]=[_THISROW].[Name], TODAY()>=[Date], “Vacation”=[Request Type])))

Edit: Corrected the spelling of “Hours Used” field in the expression

2 Likes

@Suvrutt_Gurjar has you covered on this one. :slight_smile:

2 Likes

Thank you!!!

1 Like

This formula is great for an individual to see their own information. If I were to use this same theory but for the manager to be able to select an employee and see their remaining time, how would I do that

I know the _ThisRow essentially limits the view to the employee in the the app. I took the ThisRow out but I don’t think that is the answer.

Is there any way for the app to know if the current user is a manager?