+ Reply to Thread
Results 1 to 2 of 2

SUMIFS Data Validation Blank Select All

  1. #1
    Registered User
    Join Date
    02-07-2022
    Location
    Melbourne, Australia
    MS-Off Ver
    MS Office 365
    Posts
    4

    SUMIFS Data Validation Blank Select All

    Hi,
    I am trying to work this out and haven't had any luck. I have a Resource Planning Template that I found on the internet and I am trying to understand what is being done...

    If you have a look on the 'Resource Summary' page, in cell C3 there is a data list where when the project is selected it filters the numbers below. I understand that it is using SUMIFS to achieve this. I have tried to recreate this sheet in a seperate workbook, but everytime I use the SUMIFS, when C3 is empty, nothing pops up, whilst here it has gone ahead and has selected all the projects.

    For e.g. under Person 1 - even though C3 is empty, it is still showing the aggregate for all the hours. I have tried everything including copying the formula but unfortunately I have not had much luck.

    Would you be able to explain how this has been achieved? I essentially want to recreate the spreadsheet where having C3 as blank will still show all the numbers and it will only then filter thereafter.

    The document has been attached for reference. Thank you
    Attached Files Attached Files

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: SUMIFS Data Validation Blank Select All

    Hi,

    Unhide row 2. You'll see a formula in cell C2 which is being used in the formulas and which, if the dropdown in C3 is blank, returns "*", which, used as a wildcard in SUMIFS, will return a value for all projects.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Select the blank range and set the data validation
    By rajuganapathy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-13-2019, 01:20 PM
  2. [SOLVED] Sumifs with data validation
    By Alba573 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-07-2019, 10:23 AM
  3. Multi-select from data validation isn't working with auto-assigning data validation
    By iPenguin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-11-2017, 12:37 PM
  4. Replies: 1
    Last Post: 07-22-2016, 04:03 PM
  5. Need help with SumIfs & Data validation
    By Mr Nat in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-05-2014, 12:52 PM
  6. [SOLVED] SUMIFS & Data Validation
    By SChapman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-22-2013, 03:40 PM
  7. Using Defined Names with Data Validation Depend and Data Validation Multi Select
    By Vinnie Chan in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-01-2012, 05:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1