SUMIF Formula

kgib

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

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

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

Mike TV

@kgib

https://help.smartsheet.com/function/sumifs

Genevieve P.

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

@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.

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

@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

Capture.PNG

?

Genevieve P.

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:

• Create cross sheet references to work with data in another sheet
• Create a Cell or Column Reference in a Formula
• Formula Foundations Webinars

Cheers,

Genevieve