if function search in 2 columns

I currently have if functions to count and sum how many wins a rep has however we are now introducing teams into the mix. I was wondering if i could have it sum anything that has either a sales rep name or team name.

the 2 formulas i have now are below with the rep or teams name (dynamic).

can i have it search for either a match in the rep or the team column rather than having to change the formula each time i change it from a rep to team or visa versa?

=SUMIFS([Quote Value Ex GST]:[Quote Value Ex GST], [Sales Rep]:[Sales Rep], =$[M-1]@row, [Won Date]:[Won Date], AND(IFERROR(YEAR(@cell), 0) = $[M-1]$17, IFERROR(MONTH(@cell), 0) = $[M-7]$23))

=SUMIFS([Quote Value Ex GST]:[Quote Value Ex GST], [Team]:[Team], =$[M-1]@row, [Won Date]:[Won Date], AND(IFERROR(YEAR(@cell), 0) = $[M-1]$17, IFERROR(MONTH(@cell), 0) = $[M-7]$23))



Answers

  • Hi @Sian.G

    Will there ever be instances where the same row has both a Sales Rep and a Team that has the value you have in [M-1]@row?

    If it will always be either-or, you could simply add the two SUMIFs together:

    =SUMIFS([Quote Value Ex GST]:[Quote Value Ex GST], [Sales Rep]:[Sales Rep], =$[M-1]@row, [Won Date]:[Won Date], AND(IFERROR(YEAR(@cell), 0) = $[M-1]$17, IFERROR(MONTH(@cell), 0) = $[M-7]$23)) + SUMIFS([Quote Value Ex GST]:[Quote Value Ex GST], [Team]:[Team], =$[M-1]@row, [Won Date]:[Won Date], AND(IFERROR(YEAR(@cell), 0) = $[M-1]$17, IFERROR(MONTH(@cell), 0) = $[M-7]$23))


    However if there are instances where it's just the Sales, and just the Team, as well as a combination of both on the same row, then we would need to subtract the rows where there's a combination from the equation above.

    This is the structure of the formula:

    =(SUMIFS(Sales Rep) + SUMIFS(Team)) - SUMIFS(Sales Rep AND Team same row)


    Try:

    =(SUMIFS([Quote Value Ex GST]:[Quote Value Ex GST], [Sales Rep]:[Sales Rep], =$[M-1]@row, [Won Date]:[Won Date], AND(IFERROR(YEAR(@cell), 0) = $[M-1]$17, IFERROR(MONTH(@cell), 0) = $[M-7]$23)) + SUMIFS([Quote Value Ex GST]:[Quote Value Ex GST], [Team]:[Team], =$[M-1]@row, [Won Date]:[Won Date], AND(IFERROR(YEAR(@cell), 0) = $[M-1]$17, IFERROR(MONTH(@cell), 0) = $[M-7]$23))) - SUMIFS([Quote Value Ex GST]:[Quote Value Ex GST], [Sales Rep]:[Sales Rep], =$[M-1]@row, [Team]:[Team], =$[M-1]@row, [Won Date]:[Won Date], AND(IFERROR(YEAR(@cell), 0) = $[M-1]$17, IFERROR(MONTH(@cell), 0) = $[M-7]$23))


    Let me know if I've understood what it is you're looking to do and if this works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!