Combine: IF AND CONTAINS, And SUMIF

A Rose

Hi,

I got a question about how to use all the above statements in one formula,

_______

I have the following columns;

A regular text column "Client ID",

A multiple dropdown column "Shared IDs",

A regular text column "Hours worked"

A regular text column "Shared Hours Approved"

_______

I have each client and their Client ID on a separate row,

If a client shares their ID with another client, it'll have their ID and the other client's ID in the "Shared ID" multiple dropdown column,

Then I want to check if their shared hours together are approved, for that;

I need to calculate all clients who have their "Client ID" in the "Shared ID" column, AND their accumulated "Hours Worked" are less than the "Shared Hours Approved"

If it found such a match and it's less than approved, show "Approved".

_______

My formula is as follows:

=IF(AND(CONTAINS([Client ID]@row, [Shared ID]:[Shared ID]), SUMIF([Hours worked]:[Hours worked] <= [Shared Hours Approved]@row)), "Approved")

_______

Error message: #INCORRECT ARGUMENT SET

_______

Please help :)

_______

Thank you!

Genevieve P.

Hey @A Rose

I agree that it would be helpful to see screen captures! I'm imagining something like this, but I don't quite understand what it is you're looking to calculate:

Here's an adjustment of your formula:

=IF(AND(CONTAINS([Client ID]@row, [Shared ID]:[Shared ID]), SUMIFS([Hours worked]:[Hours worked], [Shared ID]:[Shared ID], HAS(@cell, [Client ID]@row)) <= [Shared Hours Approved]@row), "Approved")

The SUMIFS is Summing together the values in the Hours Worked column for all the rows where the Client ID is in the Shared IDs column. Is that what you wanted it to do?

So in the instance above, it's finding that Client 1 and Client 4 together worked 8 hours, but the shared hours approved is set as 6 (or did you want the SUM that to be 12?)

Genevieve P.

Hi @A Rose

Try this:

=IF(AND(CONTAINS([Client ID]@row, [Shared ID]:[Shared ID]), SUMIFS([Hours worked]:[Hours worked], [Shared ID]:[Shared ID], HAS(@cell, [Client ID]@row)) <= [Shared Hours Approved]@row), "Approved", SUMIFS([Hours worked]:[Hours worked], [Shared ID]:[Shared ID], HAS(@cell, [Client ID]@row)) - [Shared Hours Approved]@row)

This says that if the first statement is not true (so if the Shared Hours Approved in this current row is NOT less than or equal to the SUM of all the hours associated), then do that same SUMIFS and subtract the current row Shared Hours Approved from it.

Cheers,

Genevieve

Paul Newcome

Are you able to provide screenshots for context?

Genevieve P.

Hey @A Rose

I agree that it would be helpful to see screen captures! I'm imagining something like this, but I don't quite understand what it is you're looking to calculate:

Here's an adjustment of your formula:

=IF(AND(CONTAINS([Client ID]@row, [Shared ID]:[Shared ID]), SUMIFS([Hours worked]:[Hours worked], [Shared ID]:[Shared ID], HAS(@cell, [Client ID]@row)) <= [Shared Hours Approved]@row), "Approved")

The SUMIFS is Summing together the values in the Hours Worked column for all the rows where the Client ID is in the Shared IDs column. Is that what you wanted it to do?

So in the instance above, it's finding that Client 1 and Client 4 together worked 8 hours, but the shared hours approved is set as 6 (or did you want the SUM that to be 12?)

A Rose

Hi @Genevieve P.

Looks like you got it! I'll review it again and update if this works for me,

Thanks,

A Rose

Hi @Genevieve P.

That worked! thank you so much!

Now, can we take this even further, by indicating when it's more than approved, how much more it is?

I figured based on your formula, how to say "More Than Approved" when it exceeds, but -instead of just saying more than approved - how do I indicate an amount how much more it is?

Updated formula:

=IF(AND(CONTAINS([Client ID]@row, [Shared ID]:[Shared ID]), SUMIFS([Hours worked]:[Hours worked], [Shared ID]:[Shared ID], HAS(@cell, [Client ID]@row)) <= [Shared Hours Approved]@row), "Approved",IF(AND(CONTAINS([Client ID]@row, [Shared ID]:[Shared ID]), SUMIFS([Hours worked]:[Hours worked], [Shared ID]:[Shared ID], HAS(@cell, [Client ID]@row)) > [Shared Hours Approved]@row), "More Than Approved")

Thank you!

Genevieve P.

Hi @A Rose

Try this:

=IF(AND(CONTAINS([Client ID]@row, [Shared ID]:[Shared ID]), SUMIFS([Hours worked]:[Hours worked], [Shared ID]:[Shared ID], HAS(@cell, [Client ID]@row)) <= [Shared Hours Approved]@row), "Approved", SUMIFS([Hours worked]:[Hours worked], [Shared ID]:[Shared ID], HAS(@cell, [Client ID]@row)) - [Shared Hours Approved]@row)

This says that if the first statement is not true (so if the Shared Hours Approved in this current row is NOT less than or equal to the SUM of all the hours associated), then do that same SUMIFS and subtract the current row Shared Hours Approved from it.

Cheers,

Genevieve

A Rose

Amazing! you made that so simple for me :)

Thank you so much @Genevieve P. !