Combine: IF AND CONTAINS, And SUMIF

Options

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!

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide screenshots for context?

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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
    A Rose ✭✭✭✭✭
    Options

    Hi @Genevieve P.

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

    Thanks,

  • A Rose
    A Rose ✭✭✭✭✭
    Options

    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.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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
    A Rose ✭✭✭✭✭
    Options

    Amazing! you made that so simple for me :)

    Thank you so much @Genevieve P. !

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!