SUMPRODUCT that filters result with each new condition added

[iantix]

Hello all,

I have a large dataset for email data in one tab - called ALL TIME - and in my STATS sheet, I am using the following SUMPRODUCT to fetch it based on several criteria; year, quarter, model, type of email, etc

Please Login or Register  to view this content.

BUT when I want to have a query which starts by showing all data for that year, then when I add quarter, it just shows that, then when I add email type, just shows by year/quarter/email type and so on.

I am currently using;

Please Login or Register  to view this content.

But it needs all fields filled in in the filter cells G84 down to G87 to show a result, and doesn't start by showing all data for year if filter 1 is filled, but filters 2 onwards are blank.

Example;

• Cell G84 shows year selected
• Cell G85-87 are empty
• Data returned should be for whole year
  
• BUT
• When I add the Quarter to cell G85, it should adjust the result to show data from that year and then only that quarter.
• It should filter down further if anything in the range from the first two criteria is found for filters 3 in cell G86, and then filter 4 in cell G87

I'm not easily able to strip out and attach a sheet at this stage due to the nature of my source file, so have tried to add this info above.

Hoping someone can help steer.

Thanks,

Ian

[Olly]

The DSUM formula will work perfectly for you. Have a read here: https://www.techonthenet.com/excel/formulas/dsum.php

[iantix]

Thanks -many of my dropdown criteria are text strings, and also the columns that are searched in the SUMPRODUCT are not adjacent - will this work in those cases?

[iantix]

Yup - tried this but;

Please Login or Register  to view this content.

How does the formula know which column to look down for the results... I defaulted the middle digit to 1 as I simply can't adapt the page info you gave my needs?

[iantix]

Okay - have tried this;

Please Login or Register  to view this content.

And it does alter result with each extra criteria added in G84, G85, etc - but seems to be adding or mutiplying rather than filtering down with each extra criteria.

Anyone got any steer please?

Ian

[Olly]

Have a look at this attachment, for a worked example of DSUM.

[iantix]

Thanks - how does the formula to know to look at sheet1 on your example?
I ask as I'll need to ensure it looks at my sheet name.

[iantix]

@Olly - have managed to get it working- but problem I'm finding with this formula;

Please Login or Register  to view this content.

Is that if I go in order - select year, then quarter, then car model, then email type - all fine.
But if year, quarter, or model are blank, then email type alone returns no values.
Same true if I do year, then car model without selecting quarter.

So it's part of the way there - but needs to return values even if not all criteria filled, and if criteria are entered in different order/not at all.

[Olly]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[iantix]

@Olly - sorry for the delay - as in my OP, I was reluctant to put a trial sheet simply as the sheet is VERY hard to desensitize - that said, it's now attached.
You'll see my STATS page has the filter - the ALL TIME sheet has the data and the LISTS sheet is just that.
Ignore the blank columns in ALL TIME which do need to be there in - I simply removed the contents of them for this purpose.
The filter works with YEAR, and then when QUARTER added, then stops output with further filter.

Also, I need this to work if YEAR is blank but I add Q1 (for example) - and it outputs all Q1 regardless of year.
Same is true is I select MODEL and not YEAR or QUARTER, or EMAIL TYPE, and no other criteria.

If I removed a criteria, I need the output to adjust - removing that 'filter'

Thoughts?

[Olly]

The only error I can see is your field header in Stats!D1 doesn't match the source field header in 'ALL TIME'!L2.

Change this from 'Model Feat.' to 'Model Featured' and it does exactly what you want.


Looking at what you're trying to achieve though, I would be using a pivot table... This saves you having to maintain the list of distinct values for each field, it's really easy to adapt to a dynamic source range and refresh, and you can use slicers to control multiple pivot table views of your data. A quick example is in the attached file.

[iantix]

@Olly - thanks for the header info - appreciated and something I should have spotted.

My one blindspot is pivots - I need to find a formula based solution, so can we assume pivots don't exist for the purposes of this. Also, the sheet is being built by me but used by colleagues with less excel knowledge so needs to be a simply dropdown select filter as specified.

HAVING SAID ALL OF THAT - fixing the header names for MODEL FEATURED and TYPE (not Email Type as I had) fixes things and it works as I need it to - so DSUM does do what I specified in my post #10 - I'd just not matched the column names properly. Thank you for the solution.

[Olly]

My one blindspot is pivots - I need to find a formula based solution, so can we assume pivots don't exist for the purposes of this. Also, the sheet is being built by me but used by colleagues with less excel knowledge so needs to be a simply dropdown select filter as specified.

I'd strongly recommend you get over your reluctance to use pivots - they're usually a far more robust, versatile and scaleable solution than fixed formulae!

However - if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You can also Add Reputation to those who helped you. Thanks.

[iantix]

Hi, it's both a reluctance and also a necessity - I'd never assume that most folk has a knowledge of pivots and that's proven true in most cases. If I myself know them really well but I'm creating a sheet for use by those who do not, then that rarely works unless every company had a view to ensure full training is paid for (in time and money sense). For now - I need to fully understand the formula action, and that logic is (I find) easier for people to adopt.
Will of course marked solved - thanks again.

[Olly]

With respect, you yourself didn't have the understanding to make your 'SUMPRODUCT' approach work... so it's a pretty safe bet that your users won't find the DSUM solution any more or less new and unknown than a pivot table...

One of the best things about pivot tables is that end users don't have to understand them, to use them. Especially when you use well placed slicers, the reports then become intuitive click / get results. Most end users don't need to concern themselves with HOW the result is presented.

If the concern is for auditing and supporting, rather than training end users, then again I suggest Pivot Tables are more robust. Your data and business logic is stored in ONE place, and become reuseable.

The other consideration, is being able to scale up to Modern Excel - Power Query, Power Pivot, Power BI. It's much easier to begin to understand these new (and vastly improved) ways of manipulating and presenting data, with a solid understanding and use of existing pivot tables.

Interesting stuff

[iantix]

Thanks - with respect - I'm trying to learn, hence me asking here, and in using these solutions, I'll improve and adapt.
If pivots become a business necessity for the company, then I'm sure I and others will go there.