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