+ Reply to Thread
Results 1 to 11 of 11

IF cells in a range are empty, reference values in another cell range

  1. #1
    Registered User
    Join Date
    01-13-2021
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel O365 Enterprise
    Posts
    10

    Question IF cells in a range are empty, reference values in another cell range

    Hi everyone, I am new to this forum and hoping to get some input.

    I have a large table of data and I am using a dynamic sumproduct formula (integrating the offset function) to calculate the total instances that meet two criteria for a specific category.

    Current calculation: total instances for Category 1 that meet the below two criteria
    Search Criteria: Variable 1: "X" / Test 1: "A"
    Formula: =SUMPRODUCT(SUBTOTAL(3,OFFSET($B$11:$B$16,ROW($B$11:$B$16)-MIN(ROW($B$11:$B$16)),,1)),(--EXACT(D5,$B$11:$B$16)),(--EXACT($C$6,$C$11:$C$16))+0)
    Answer = 3

    Problem: I am slowly building out a second set of test results and I am looking to report on the latest data by migrating to Test 2 results as they become available. Therefore, I need to adjust the formula above to test a third variable in the formula but with an additional condition. This condition needs to first search the Test 2 data range for the variable “A”. If it finds different values, those rows will not be counted. If it identifies blank/empty cells, the formula needs to alternatively search in the Test 1 data range for “A”.
    Answer = 3

    Note: I have been playing with ISBLANK/AND functions but can’t seem to make anything work.

    Any assistance is greatly appreciated 😊
    KJ
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by KJ_; 01-13-2021 at 06:23 PM. Reason: Adding attachment

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: IF cells in a range are empty, reference values in another cell range

    Could you attach a sample worksheet?
    Quang PT

  3. #3
    Registered User
    Join Date
    01-13-2021
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel O365 Enterprise
    Posts
    10

    Re: IF cells in a range are empty, reference values in another cell range

    Thanks so much for the quick response. I have updated the original post and attached the sample workbook!

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: IF cells in a range are empty, reference values in another cell range

    Do you really need to sum up only visible rows? I can see you are using OFFSET.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: IF cells in a range are empty, reference values in another cell range

    For the test 2, the answer is 2. Could you tell me which rows are counted? 12 and 13?

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: IF cells in a range are empty, reference values in another cell range

    For Test 2 in E6 ARRAY formula

    =SUM(IF($C$11:$C$16=$C6,IF($D$11:$D$16=E$5,1,IF($B$11:$B$16="A",1,""))))

    To enter ARRAY formula
    Copy and paste the formula in cell
    Press F2
    Press Ctrl+Shift+Enter together
    Excel covers the formula with {}.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  7. #7
    Registered User
    Join Date
    01-13-2021
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel O365 Enterprise
    Posts
    10
    The OFFSET is required to filter against different variables in other circumstances.

    Correction, answer = 3. Rows 11, 12, 13 where:

    Row 11: Variable = "X", Test 2 result = empty, therefore this row specifically relies on the empty cell condition, where Test 2 is empty, the formula needs to alternatively revert to test the result in the Test 1 range which in this instance contains the required “A” result
    Row 12: Variable = "X" / Test 2 result = "A"
    Row 13: Variable = "X" / Test 2 result = "A"

    Rows that don't meet the criteria:
    Row 14: Variable = "X" / Test 2 result = empty / Test 1 result = "B"
    Row 15: Variable = "C" / Test 2 result = "A"
    Row 16: Variable = "X" / Test 2 result = "C"
    Last edited by KJ_; 01-13-2021 at 06:21 PM. Reason: Miscalculation

  8. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: IF cells in a range are empty, reference values in another cell range

    Have you seen post #6

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: IF cells in a range are empty, reference values in another cell range

    Try:

    =SUMPRODUCT((C11:C16=$C$6)*((D11:D16=$D$5)+(D11:D16="")*(B11:B16=$D$5)))

  10. #10
    Registered User
    Join Date
    01-13-2021
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel O365 Enterprise
    Posts
    10

    Re: IF cells in a range are empty, reference values in another cell range

    Quote Originally Posted by kvsrinivasamurthy View Post
    Have you seen post #6
    Thanks for this suggestion , unfortunately this formula doesn't get the desired result. It looks like it counts rows 11,12,13,16 (=4) where rows 11,12,13 are correct (=3). Row 16 has a result in the Test 2 range (albeit a result we are not looking for in "C"). Given the intention is to migrate to Test 2 results only, there is only a need to revert to looking for results in the Test 1 range if we are still missing a result (empty cell) in the Test 2 range. I am also trying to avoid array formulas as much as I can to maintain usability.

  11. #11
    Registered User
    Join Date
    01-13-2021
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel O365 Enterprise
    Posts
    10

    Re: IF cells in a range are empty, reference values in another cell range

    Quote Originally Posted by bebo021999 View Post
    Try:

    =SUMPRODUCT((C11:C16=$C$6)*((D11:D16=$D$5)+(D11:D16="")*(B11:B16=$D$5)))
    Amazing, this appears to do the job! Would it possible to explain how this is constructed - I understand what each sub component is searching for but can't fully grasp how the calculation works, particularly with the multiplication (I assume this represents a wildcard of some description)? My interpretation so far is as follows:

    Searches for:
    1. Variable 1 "X" criteria;
    2. Test 2 "A" criteria with a condition (*) to identify and action an empty cell;
    3. if Test 2 is identified as empty, the alternate search is in Test 1 for "A" criteria

    I have integrated this back in to the SUMPRODUCT/OFFSET formula to calculate visible rows only enabling more slicing/filtering of the broader dataset against other variables:
    =SUMPRODUCT(SUBTOTAL(3,OFFSET($C$11:$C$16,ROW($C$11:$C$16)-MIN(ROW($C$11:$C$16)),,1)),(C11:C16=$C$6)*((D11:D16=$D$5)+(D11:D16="")*(B11:B16=$D$5))+0)

    Thanks in advance
    Last edited by KJ_; 01-14-2021 at 09:51 PM. Reason: Updated

+ 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. [SOLVED] count values in one range if the cells in a corresponding range are not empty
    By Thurgel in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-09-2020, 10:22 AM
  2. [SOLVED] How to create a dynamic range if the last cell is empty and the columns have empty cells
    By peter_swe in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-03-2016, 11:18 PM
  3. Replies: 0
    Last Post: 08-03-2016, 07:01 PM
  4. Replies: 5
    Last Post: 08-21-2014, 09:10 AM
  5. [SOLVED] Transfer cells range to another sheet if first cell of destination range is empty
    By kopapa in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 11-04-2013, 04:32 PM
  6. If the range A1:F1 and the cell H1 are not empty, then all range cells must be not em
    By loroverde in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-29-2013, 04:13 AM
  7. MACRO to empty a range of cells based on whether specified cell is empty
    By TBJV in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2013, 10:35 PM

Tags for this Thread

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