SUMIFS formula

Keith
Keith
edited 12/09/19 in Formulas and Functions

=SUMIFS({Completed date}, MONTH(@cell) = [Jan 2018]$1, {S/S}, AND({Rep} = "Paul"))

 

Hi All,

Trying to get the above formula to work.

Referencing from another sheet. Want to be able to match a sales reps figures per month.

From these columns in the other sheet: Completed date column, Invoice value column and rep column.

So essentially the above formula is trying to pull the sales figures for Paul for Jan 2018.

I've got a formula that will give me sales figures for the whole company for Jan 2018 but now need figures by Rep.

Below is formula that works for company sales figures:

=SUMIF({Completed date range}, MONTH(@cell) = [Jan 2018]$1, {S/S value})

Thanks.

Tags:

Comments

  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    Hi Keith,

    I can't actually work out why your company sales figures formula works. Because it shouldn't. SUMIF/SUMIFS work like this:

    =SUMIF({What you want to sum}, {the range you want to match criteria against}, "The criteria you want to match")

    Your working formula has things around the wrong way (criteria before range) and it strikes me that you're looking to SUM {S/S Value}, not {Completed date range}.

    SUMIFS also don't like dates very much, so normally you'd have to put in a workaround formula.

    How are your dates formatted? Are they just in MM/DD/YY format and you're looking to match everything with the MM/YY that you have in {Completed date range}?

    Assuming this is the case, you could try:

    =SUMIFS({S/S Value}, {Completed date range}, LEFT(@cell, 2) + RIGHT(@cell, 2) = 1718, {Rep}, "Paul")

    Kind regards,

    Chris McKay

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!