Sumifs in PowerPivot


Board Regular
Jun 3, 2015
Hi All,

I've browsed around trying to find an answer to this and have found answers which I've been able to recreate and get working (situationally) for Sumif (singular condition). I need a sumif with two variable conditions rather than fixed.

Basically the two tables I have are like this. 1 shows new lines launched, with a launch date, the other shows sales. I need to measure the impact which sending out free samples has on the first three months of sales - should be easy :P

New Lines:

[Product ID] [Samples sent?] [Launch Month] [Second Month] [Third Month] [Sales in First Month] [Sales in Second Month] [Sales in Third Month]
eg 1234....... yes................... Jan 2015.......... Feb 2015........ Mar 2015.......... SUMIFS()............... SUMIFS().................... SUMIFS()
eg 2345....... No.................... Apr 2015.......... May 2015....... Jun 2015........... SUMIFS()............... SUMIFS().................... SUMIFS()

Sales Table

[Product ID] [Invoice Month] [Sales Value]
eg 1234......... Mar 2015......... 200

Now for normal excel I would do the equivalent of Sumifs(Sales.[Sales Value],Sales.[Invoice Month],[Launch Month],Sales.[Product ID],[Product ID])

I've tried playing around with filters but they don't seem to like having a variable condition, I've also tried playing around with the calculate function - but that doesn't seem to like having multiple filters... I'm kind of going round in circles here and can't find the answers I need on the forums here.

The final step will be easy, build a pivot table to compare first 3 months of sales for all products where samples were sent, vs all products where they weren't :) I can do that!!

Thanks in advance for your help. I love these forums and really enjoy learning more tips and tricks
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
I found a solution. It's funny how sometimes just stating these things out loud can help you to work things through. The solution which worked for me was to go into the Sales table and Create a new column with a big if in it

=IF(RELATED('New Lines'[Launch Month])=[Invoice Month],"First Month",IF(RELATED('New Lines'[Second Month])=[Invoice Month],"Second Month",IF(RELATED('New Lines'[Third Month])=[Invoice Month],"Third Month",BLANK())))

Now I can build a PivotTable with "Months After Launch" as a Column Label - we're good :)

I get the sense though that there's a neater way and if there is then I'd love to learn it.
Upvote 0
Does your data already have multiple month columns for each row?

If not, can't you just drag [Product ID] to the rows section of the pivot table and [Month] to the column section of the pivot table?
Upvote 0
Sadly I can't just drag month in like that. The reason being is that product launches happen throughout the year, so a product launched in January will have Month 1 as January, Month 2 as February, Month 3 as March - but something launched in April will have Month 1 as April, Month 2 as May, Month 3 as June. All products need to be monitored across their first three months of performance both for customers that receive samples and those that don't. This is all being done so we can measure the 3 month impact of sending out free samples.
Upvote 0
If you were to add a Calculated Column to the Power Pivot table that returned "Month 1", "Month 2", "Month 3", and "Exclude Me" then you could drag that column onto the columns in the Pivot table and filter out "Exclude Me".
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back