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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!