7 Replies Latest reply on Sep 30, 2013 9:43 AM by Matt Lutton Branched from an earlier discussion.

# Replicating Excel's SUMIFS function (part two)

See attached Excel document. I'm trying to create the "Category Impressions" column in Tableau similar to how I created it in Excel. Please see attached Excel document to see the formula used. (Basically sumifs based on the Index Categories column).

• ###### 1. Re: Replicating Excel's SUMIFS function (part two)

Something like:

if [Index Categories] = 'Age Range' THEN SUM([Impressions])

You should be able to just put [Index Categories] on the Rows shelf, and SUM([Impressions]) next to it, though, to arrive at the same answer I would think, unless I am missing something.

Also, you didn't post this in the "Forum' section of the community--you'll get more responses there.  If you can post a workbook that demonstrates your data set, I can help you out.

• ###### 2. Re: Replicating Excel's SUMIFS function (part two)

Also, now I see that in your earlier thread, someone already helped you arrive at a solution.  Is there a problem with that solution?

• ###### 3. Re: Replicating Excel's SUMIFS function (part two)

The previous answer worked perfectly. However then I added the "taxonomy 3" column and then it was no longer grouping based on the "Index Category" column. Also per your first response I can't use that because I need this new field to create other fields. So I don't think I can just do that can I?

• ###### 4. Re: Replicating Excel's SUMIFS function (part two)

Let's see the Tableau packaged workbook, and we'll find out.  Since the previous solution used a Table Calculation, which is dependent on the dimensions in the view, once you added a new dimension, it probably "broke".  All that would be required is changing the advanced Compute Using settings for that Table Calc (I am making a lot of assumptions here).

How about you post that workbook with the previous solution, and I'll help you add the other column.  If you are going to need additional columns, either add them and leave the workbook "broken", or mock up which columns you need (mock up what the solution should look like)

Thanks!

• ###### 5. Re: Re: Replicating Excel's SUMIFS function (part two)

Does this work?  I'm not sure why he was using data blending, I think I got the same results just by using a WINDOW_SUM on your impressions field.  See the attached--the advanced 'Compute Using' settings are set to address the dimensions on the rows shelf (from left to right, if that makes sense).

• ###### 6. Re: Re: Replicating Excel's SUMIFS function (part two)

Matthew- Thanks this seems to work for the worksheet it's on but when you remove the taxonomy 3 then it messes up the calculation. So I'm nervous to create additional calculated fields based on this category field. Do you think it would be better to have this Category Impressions field calculated in the raw data before feeding into Excel?

• ###### 7. Re: Re: Replicating Excel's SUMIFS function (part two)

That is because Table Calculations are computed based on the dimensions in the view--you remove one, the calculation breaks.

Its up to you how you approach it--you can do all of this in Tableau, but you'll have to learn some basic Table Calculations (which will make you a much more effective Tableau user).  I am all for doing as much on the back end as possible, myself, but that doesn't make it the right solution for every scenario.

For more on Table Calcs, I'd start here: Want to Learn Table Calculations? Here’s How! | Drawing with Numbers