Sumif condition - Appsheet Style

[John_Krenkel]

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.

[Steve]

Click through this link:

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

How do I do SUMIF() or SUMIFS()? SUM( SELECT( Orders[Total Amount], AND( ([Customer] = [_THISROW].[Customer]), ([Order Date] >= [_THISROW].[Order Date]) ) ) ) Use SELECT() to gather values from a single column only. See also: AND(), SUM()

[John_Krenkel]

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]

)
)
)

[Steve]

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

[John_Krenkel]

@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

[John_Krenkel]

@Steve

The app

12 hours used across the board

Vacation = 20
Sick = 8

[John_Krenkel]

@Steve

[Steve]

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

[John_Krenkel]

@Steve

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

[John_Krenkel]

@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???

[John_Krenkel]

[Suvrutt_Gurjar]

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

[John_Krenkel]

Suvrutt_Gurjar:

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

Thank you!!!

[John_Krenkel]

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.

[Steve]

John_Krenkel:

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

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

[Steve]

@Suvrutt_Gurjar has you covered on this one.

[gdelfava]

Hi, I need help with an expression please. I am building a fleet management app and need to perform a sumif expression. 

I have two tables a trips table and a stats table as seen below. What I am attempting to sum is the distance travelled per month.

As you can see my sheets formula checks the trips table for the distance travelled and the month in which the trip occurred and then checks if the month is the same in the stats table row and does the calculation. 

I need help as I am not sure how to add this formula as an expression in appsheet. The conditions that have to be met for the calculation to work is what is throwing me.

[Steve]

Please start a new topic for your question.

[gdelfava]

I have done so.