+ Reply to Thread
Results 1 to 10 of 10

Getting an array to ignore blank cells

  1. #1
    Registered User
    Join Date
    01-24-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    11

    Getting an array to ignore blank cells

    I was given the following array formula "=SUM(--(D$3:D$117>=E$3:E$117))" to use on my spread sheet. The formula works well, however, there are blank cells with in the coloum which i can't delete but would like the array formula to ignore when it runs? Any help would be muchly appreciated.

    Michael
    Last edited by Mike001; 01-25-2013 at 08:54 AM.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Getting an array to ignore blank cells

    hi Mike001, try:
    =SUMPRODUCT((D$3:D$117>=E$3:E$117)*(D$3:D$117<>"")*(E$3:E$117<>""))

    SUMPRODUCT works a little faster than an SUM array

  3. #3
    Registered User
    Join Date
    01-24-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Getting an array to ignore blank cells

    Quote Originally Posted by benishiryo View Post
    hi Mike001, try:
    =SUMPRODUCT((D$3:D$117>=E$3:E$117)*(D$3:D$117<>"")*(E$3:E$117<>""))

    SUMPRODUCT works a little faster than an SUM array
    Hi there, thank you very much this did work. Do you know if this would work for calculating the same thing in a row instead of a coloum?

  4. #4
    Registered User
    Join Date
    01-24-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Getting an array to ignore blank cells

    Quote Originally Posted by benishiryo
    http://www.excelforum.com/excel-form...ank-cells.html

    hi there. why not try it out? =)
    it's hard to know exactly what you need without seeing the file. if you face problems, upload a sample excel file in the thread. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon. ideally, it should contain your desired results

    if you're satisfied with the answer, please mark it as "Solved". my signature in the thread will guide you how to do that. thanks
    I have attached the workbook as I can't see how the same formula would work. If you wouldn't mind having a look for me that would be great.
    Attached Files Attached Files

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

    Re: Getting an array to ignore blank cells

    You want to get the SUM of Actual-Sum of Target for each person (each row)?
    Try this for first row and copy down:
    =SUMIF($D$2:$AQ$2,D$2,$D3:$AQ3)-SUMIF($D$2:$AQ$2,E$2,$D3:$AQ3)

  6. #6
    Registered User
    Join Date
    01-24-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Getting an array to ignore blank cells

    Quote Originally Posted by bebo021999 View Post
    You want to get the SUM of Actual-Sum of Target for each person (each row)?
    Try this for first row and copy down:
    =SUMIF($D$2:$AQ$2,D$2,$D3:$AQ3)-SUMIF($D$2:$AQ$2,E$2,$D3:$AQ3)
    I am trying to count how many of the colored cells are a certain color eg. No of cells colored - Green, Amber & Red

  7. #7
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Getting an array to ignore blank cells

    Hi Mike,

    Is this not what you have in rows 120, 121 and 122?

  8. #8
    Registered User
    Join Date
    01-24-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Getting an array to ignore blank cells

    Quote Originally Posted by djapigo View Post
    Hi Mike,

    Is this not what you have in rows 120, 121 and 122?
    Yes, however, I need a formula at the end of each row eg. end of row 3

  9. #9
    Registered User
    Join Date
    01-24-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Getting an array to ignore blank cells

    Quote Originally Posted by djapigo View Post
    Hi Mike,

    Is this not what you have in rows 120, 121 and 122?
    Yes, however, I need a formula at the end of each row eg. end of row 3

  10. #10
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Getting an array to ignore blank cells

    Mike...

    I've revised my formulas... it used to be really ugly...

    AT3: =SUMPRODUCT(($D$2:$AP$2="Actual")*(D3:AP3>=E3:AQ3)*(D3:AP3>0))

    AU3: =SUMPRODUCT(($D$2:$AP$2="Actual")*(D3:AP3<E3:AQ3)*(D3:AP3>0))-AV3

    AV3: =SUMPRODUCT(($D$2:$AP$2="Actual")*(D3:AP3<E3:AQ3-6)*(D3:AP3>0))
    Last edited by djapigo; 01-25-2013 at 06:53 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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