+ Reply to Thread
Results 1 to 15 of 15

SUMIFS with blank cells in criteria

  1. 02-12-2015, 04:58 AM #1
    Frodoe
    Frodoe is offline
    Registered User
    Join Date
    02-12-2015
    Location
    South Africa
    MS-Off Ver
    10
    Posts
    31

    Lightbulb SUMIFS with blank cells in criteria

    Hi all

    Some assistance please.
    As per the attachment. Criteria is displayed in the "Totals" tab and the range and Sum Return are from the "Assortment" tab.
    I would like the option of deleting certain cells (criteria), then for the formula to display the results from only the populated criteria.

    Example. (on attachment)

    CHAIN DEPT BRAND

    AMS Footwear Adidas

    If I only want to select the above criteria and exclude the others, then I need to delete the cells (criteria) which I've highlighted in yellow. However this then returns a ZERO. What do I need to do? I tried viewing past threads but cannot make it work.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-12-2015, 05:31 AM #2
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: SUMIFS with blank cells in criteria

    Confirm with Shift+Ctrl+Enter since it is an array formula
    =SUM(INDEX(IFERROR((aa!$R$6:$R$220)*(((aa!$G$6:$G$220=A5)+(A5=""))>0)*(((aa!$H$6:$H$220=B5)+(B5=""))>0)*(((aa!$L$6:$L$220=C5)+(C5=""))>0)*(((aa!$F$6:$F$220=D5)+(D5=""))>0)*(((aa!$I$6:$I$220=E5)+(E5=""))>0)*(((aa!$J$6:$J$220=F5)+(F5=""))>0)*(((aa!K5:$K$220=G5)+(G5=""))>0),0),0))
    Try this array formula in I5 and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  3. 02-12-2015, 06:04 AM #3
    Frodoe
    Frodoe is offline
    Registered User
    Join Date
    02-12-2015
    Location
    South Africa
    MS-Off Ver
    10
    Posts
    31

    Re: SUMIFS with blank cells in criteria

    Thanks Siva but it still does not work. After pasting and confirming with Shift+Ctrl+Enter a pop up occurs requesting to "Update Value: aa". I have to select a workbook and then select the tab within the work book. Any selection made doesn't work.
    Reply With QuoteRegister To Reply
  4. 02-12-2015, 06:07 AM #4
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: SUMIFS with blank cells in criteria

    ooooooo!
    I am sorry I changed the sheet name in the downloaded file
    =SUM(INDEX(IFERROR((ASSORTMENT!$R$6:$R$220)*(((ASSORTMENT!$G$6:$G$220=A5)+(A5=""))>0)*(((ASSORTMENT!$H$6:$H$220=B5)+(B5=""))>0)*(((ASSORTMENT!$L$6:$L$220=C5)+(C5=""))>0)*(((ASSORTMENT!$F$6:$F$220=D5)+(D5=""))>0)*(((ASSORTMENT!$I$6:$I$220=E5)+(E5=""))>0)*(((ASSORTMENT!$J$6:$J$220=F5)+(F5=""))>0)*(((ASSORTMENT!K5:$K$220=G5)+(G5=""))>0),0),0)) try this
    Reply With QuoteRegister To Reply
  5. 02-12-2015, 06:12 AM #5
    Frodoe
    Frodoe is offline
    Registered User
    Join Date
    02-12-2015
    Location
    South Africa
    MS-Off Ver
    10
    Posts
    31

    Re: SUMIFS with blank cells in criteria

    It works!! however there is 1 more issue. if all the criteria are identical for rows 5,6,7 - the results in I5,6,7 differ. They should all be the same right?
    Reply With QuoteRegister To Reply
  6. 02-12-2015, 06:17 AM #6
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: SUMIFS with blank cells in criteria

    Yes, you are right
    there is $is missing before and after (((ASSORTMENT!K5:$K$220=G5)+(G5=""))>0),0),0)) try this
    please add $ sign beofre and after K
    Reply With QuoteRegister To Reply
  7. 02-12-2015, 06:17 AM #7
    Frodoe
    Frodoe is offline
    Registered User
    Join Date
    02-12-2015
    Location
    South Africa
    MS-Off Ver
    10
    Posts
    31

    Re: SUMIFS with blank cells in criteria

    CHAIN DEPT BRAND SUPPLIER SUBDEPT CAT SUBCAT UNITS W14 UNITS W15

    AMS Footwear Adidas AMICA FASHION COMPANY(PTY) LTD Mens Bags Active Bottoms 0
    AMS Footwear Adidas AMICA FASHION COMPANY(PTY) LTD Mens Bags Active Bottoms 130
    AMS Footwear Adidas AMICA FASHION COMPANY(PTY) LTD Mens Bags Active Bottoms 0
    Reply With QuoteRegister To Reply
  8. 02-12-2015, 06:18 AM #8
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: SUMIFS with blank cells in criteria

    =sum(index(iferror((assortment!$r$6:$r$220)*(((assortment!$g$6:$g$220=a5)+(a5=""))>0)*(((assortment!$h$6:$h$220=b5)+(b5=""))>0)*(((assortment!$l$6:$l$220=c5)+(c5=""))>0)*(((assortment!$f$6:$f$220=d5)+(d5=""))>0)*(((assortment!$i$6:$i$220=e5)+(e5=""))>0)*(((assortment!$j$6:$j$220=f5)+(f5=""))>0)*(((assortment!$k$5:$k$220=g5)+(g5=""))>0),0),0))
    Reply With QuoteRegister To Reply
  9. 02-12-2015, 06:20 AM #9
    Frodoe
    Frodoe is offline
    Registered User
    Join Date
    02-12-2015
    Location
    South Africa
    MS-Off Ver
    10
    Posts
    31

    Re: SUMIFS with blank cells in criteria

    Siva you are a legend!! Thank you so much!!
    Reply With QuoteRegister To Reply
  10. 02-12-2015, 06:25 AM #10
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: SUMIFS with blank cells in criteria

    You are welcome and thanks for your feedback
    Reply With QuoteRegister To Reply
  11. 02-13-2015, 03:05 AM #11
    Frodoe
    Frodoe is offline
    Registered User
    Join Date
    02-12-2015
    Location
    South Africa
    MS-Off Ver
    10
    Posts
    31

    Re: SUMIFS with blank cells in criteria

    Hi Siva

    Do you mind helping me with 1 more issue related to the same formula. I would like to use the same formula on the next 2 cells to the right of the formula cell. I've copied the formula, moved the results column in the "ASSORTMENT tab" to S & T respectively but it is not working. I am obviously missing something out.
    Reply With QuoteRegister To Reply
  12. 02-13-2015, 03:08 AM #12
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: SUMIFS with blank cells in criteria

    attach sample file what you tried, it is hard to me to fix without excel file
    Reply With QuoteRegister To Reply
  13. 02-13-2015, 03:17 AM #13
    Frodoe
    Frodoe is offline
    Registered User
    Join Date
    02-12-2015
    Location
    South Africa
    MS-Off Ver
    10
    Posts
    31

    Re: SUMIFS with blank cells in criteria

    see attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  14. 02-13-2015, 03:29 AM #14
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: SUMIFS with blank cells in criteria

    OK
    There is nothing wrong in the formula
    it is an array formula
    When ever you modified the formula, stay in the cell, press F2 then Press Shift+Ctrl+Enter keys at a time (3 keys at a time)
    See the attached file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  15. 02-13-2015, 03:37 AM #15
    Frodoe
    Frodoe is offline
    Registered User
    Join Date
    02-12-2015
    Location
    South Africa
    MS-Off Ver
    10
    Posts
    31

    Re: SUMIFS with blank cells in criteria

    Once again, Thank you Siva!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. SUMIFS: If no criteria match, leave cell blank, else return value
    By JTwrk in forum Excel General
    Replies: 5
    Last Post: 07-02-2017, 10:25 AM
  2. [SOLVED] Multiple SUMIFS based on criteria but ignores if a cell is blank..
    By Andrewbutler in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 11-23-2014, 06:30 PM
  3. [SOLVED] SUMIFS Formula: Can it ignore a Blank Criteria field?
    By mlj61289 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-17-2013, 12:43 PM
  4. SUMIFS Not Blank Criteria
    By philwilliams80 in forum Excel General
    Replies: 3
    Last Post: 09-30-2011, 11:27 AM
  5. How to ignore criteria set in SUMIFS fuction if criteria cell is blank?
    By Sam - excel rookie in forum Excel General
    Replies: 5
    Last Post: 09-07-2011, 11:14 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1