• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumifs of a date range when present in drop downs

S

SouthKaDaaku

New Member
Hi,

I have user count data of people who watched a given movie during a particular date range.

Now, there are 3 validation criteria - the movie watched, the from date and the to date. All of them are part of dropdown lists.

I would like to find out the total number of users who watched a particular movie across a date range.
For example, How many users watched Baahubali between 19th and 26th January?

The data should update automatically when the values in the dropdown are changed.

Sharing a sample file for reference.

Thanks in advance.
 

Attachments

  • Sumifs With Date Range thu Dropdowns.xlsx
    9.9 KB · Views: 9

Attachments

  • Chandoo47448Sumifs With Date Range thu Dropdowns.xlsx
    26.3 KB · Views: 7
p45cal said:
Two offerings in the attached. On Sheet1 a formula in cell J10:
=SUM(INDEX(B3:M7,MATCH(E10,A3:A7,0),MATCH(F10,B2:M2,0)):INDEX(B3:M7,MATCH(E10,A3:A7,0),MATCH(G10,B2:M2,0)))
Will need to be adjusted as the source data changes.

On Sheet1 (2) a Power Query solution similar to that offered at https://chandoo.org/forum/threads/f...nd-present-them-graphically.47424/post-281398
Click to expand...


So I had done it with Power Query previously also and it was solved properly. My boss however wanted it in a drop down format. Hence the question.

Does it mean that Power Query is a better way to tackle this problem than the multiple dropdowns? What would you prefer?

Thanks once again!
 
SouthKaDaaku said:
Does it mean that Power Query is a better way to tackle this problem than the multiple dropdowns? What would you prefer?
Click to expand...
This is totally a personal preference thing.
Dropdowns as in mine and @bosco_yip 's formula solutions:
Pros:
  • Start and End dates is easy to choose, no need expressly to include intermediate dates.
  • Small footprint on the sheet. Only 5 cells, and you can have them anywhere on the sheet.
Cons:
  • With my formula solution you have to update the formula to accommodate the source data range (with bosco_yip's less so).
  • You have to maintain the dropdowns (validation) each time the source data range changes.
Pivot table:
Pros:
  • A really wide range of display options in the pivot at the drop of a hat, including charting results, (but if you only want just that single datum that's not really a pro).
  • You can use dropdowns in a pivot table directly (you don't have to have Slicers and TimeLines) and there are other options to include ranges of dates:
77569

The dropdown lists of films and dates will maintain themselves.
Usually the source table will adjust itself to fit new data entered.

Cons:
  • You need to refresh the pivot table after a change in source data.
  • The foot print is a little larger (2x3 cells if you use the dropdowns within the pivot, and 2x1 cells if you use a slicer and timeline - but of course, the space used by the slicer and timeline is significant).

My preference: PQ direct to pivot every time.
 
p45cal said:
This is totally a personal preference thing.
Dropdowns as in mine and @bosco_yip 's formula solutions:
Pros:
  • Start and End dates is easy to choose, no need expressly to include intermediate dates.
  • Small footprint on the sheet. Only 5 cells, and you can have them anywhere on the sheet.
Cons:
  • With my formula solution you have to update the formula to accommodate the source data range (with bosco_yip's less so).
  • You have to maintain the dropdowns (validation) each time the source data range changes.
Pivot table:
Pros:
  • A really wide range of display options in the pivot at the drop of a hat, including charting results, (but if you only want just that single datum that's not really a pro).
  • You can use dropdowns in a pivot table directly (you don't have to have Slicers and TimeLines) and there are other options to include ranges of dates:
View attachment 77569

The dropdown lists of films and dates will maintain themselves.
Usually the source table will adjust itself to fit new data entered.

Cons:
  • You need to refresh the pivot table after a change in source data.
  • The foot print is a little larger (2x3 cells if you use the dropdowns within the pivot, and 2x1 cells if you use a slicer and timeline - but of course, the space used by the slicer and timeline is significant).
My preference: PQ direct to pivot every time.
Click to expand...


Thanks again!! :)
 
You must log in or register to reply here.
Back
Top