SUMIF Formula

Looking to create a SUMIFs formula and having trouble.

Add Revenue 1 to revenue 2 only IF the Month value is the same and IF the campaign tag is the same = Wyn

Answers

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭

    These guys...

    Is this a cross sheet formula? are rev.1 and rev.2 on the source sheet, does that sheet also have a month column & campaign column? On your destination sheet do you also have a month value?

  • kgib
    kgib ✭✭

    Thanks @BullandKhmer

    Here is more information on what I am trying to accomplish:

    All columns are contained in the same source sheet.

    Column Names:

    Revenue

    Campaign_Tag

    Month

    Yes

    MiniVac Monthly Call Revenue

    Looking for a SUMIFs formula that calculates the following:

    Sum Revenue and MiniVac Monthly Call Revenue IF the Campaign tag = "MiniVac-WynD" and it occurred in the same month and year

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @kgib

    It sounds like the tricky part here is the date range that you're looking for, is that what you're getting stuck on?

    Can I confirm that you're building the SUM formula within the same sheet as your data, in another column? If so, we could actually use an IF statement to then simply + together the two cells, IF the row meets your criteria.

    For example, something like this:

    =IF(AND([Campaign_Tag]@row = "MiniVac-WynD", Month@row = monthcomparison, year statement), Revenue@row + [MiniVac Monthly Call Revenue]@row, "false value")


    However we're missing a few pieces of information here, that I've bolded:

    • How are you comparing the "Month"? Is this a Date column, and are you comparing it to a different column in the sheet or are you wanting it to compare to Today's Month?
    • Where are you getting the Year from, and what Year are you comparing it to? (Today's year or a different column's year?)
    • What do you want the formula to do if the Campaign Tag is NOT "MiniVac-WynD"?

    Thanks for the clarifications!

    Cheers,

    Genevieve

  • kgib
    kgib ✭✭

    @Genevieve P. thank you SO much!

    All information is contained in the same source sheet.

    The column "month" is 1,2,3,4 and so on

    The column "year" is 2020, 2021, 2022

    I need this formula to SUM all the lines for, say, January (month 1) in year (2022)

    If the campaign tag is not MiniVac-WynD it should not do anything

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @kgib

    Thank you for this additional information! In that case, a SUMIFS is exactly the way to go.

    =SUMIFS(Revenue:Revenue, Month:Month, 1, Year:Year, 2022, [Campaign_Tag]:[Campaign_Tag], <> "MiniVac-WynD")

    However you noted two columns to SUM - Revenue & [MiniVac Monthly Call Revenue], is that correct? In this instance you can add two SUMIFS together:

    =SUMIFS(Revenue:Revenue, Month:Month, 1, Year:Year, 2022, [Campaign_Tag]:[Campaign_Tag], <> "MiniVac-WynD") + SUMIFS([MiniVac Monthly Call Revenue]:[MiniVac Monthly Call Revenue], Month:Month, 1, Year:Year, 2022, [Campaign_Tag]:[Campaign_Tag], <> "MiniVac-WynD")


    If this still hasn't helped, it would be best if we could see screen captures of your sheet set-up (with example data), but please block out anything sensitive.

    Cheers,

    Genevieve

  • kgib
    kgib ✭✭

    @Genevieve P. that did it! Thank you SO SO much!

    One more question - the formula now totals all lines that fall in the same month/year/with the same campaign tag BUT I want it to show that total on the first line only. If I copy the formula down it will show the total on every line which will skew my numbers (see attached screenshot). How do I direct this to only total on the first line of the month and show $0 for the other lines of the month

    ?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @kgib

    I would actually suggest having your calculations on a second, separate sheet.

    That way you can have just one row as the "summary" for each of your instances, and you can even make your formula more dynamic as you can reference the "Month" cell and the "Year" cell instead of typing it out.

    You'd want to use {cross-sheet references} instead of these [in-sheet] ones.

    =SUMIFS({Revenue Reference}, {Month Reference}, Month@row, {Year Reference}, Year@row, {Campaign_Tag Reference}, <> "MiniVac-WynD") + SUMIFS({MiniVac Monthly Call Revenue Reference}{Month Reference}, Month@row, {Year Reference}, Year@row, {Campaign_Tag Reference}, <> "MiniVac-WynD")


    Like so:


    Here are some Help Articles that you may find useful as you build this out:

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!