if function search in 2 columns

Sian.G

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

Genevieve P.

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