+ Reply to Thread
Results 1 to 14 of 14

I want to exclude blank cell in index match formula with multiple criteria

  1. #1
    Registered User
    Join Date
    09-07-2013
    Location
    uae
    MS-Off Ver
    Excel 2003
    Posts
    9

    I want to exclude blank cell in index match formula with multiple criteria

    In the above attachment my question is that, i want only that month in which the employee has taken the leave salary without including blank cell when i drag the formula in sheet.


    Thanks

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: I want to exclude blank cell in index match formula with multiple criteria

    Can not see any attachment but try this.
    month.xlsx
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Registered User
    Join Date
    09-07-2013
    Location
    uae
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: I want to exclude blank cell in index match formula with multiple criteria

    Thanks.
    but its not working. i will attached the sheet again.

    regards
    Noor

  4. #4
    Registered User
    Join Date
    09-07-2013
    Location
    uae
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: I want to exclude blank cell in index match formula with multiple criteria

    Please solve this issue.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,618

    Re: I want to exclude blank cell in index match formula with multiple criteria

    Like this? I haven't been consistent with cell references here, one named range is dynamic, one is not and so on.
    Nevertheless I think it turned out pretty well. Since you are using Excel 2003 I couldn't use the IFERROR so I just used Conditional Formatting to make error text white.

    I put the spin button there just for testing.
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  6. #6
    Registered User
    Join Date
    09-07-2013
    Location
    uae
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: I want to exclude blank cell in index match formula with multiple criteria

    Thank for the earlier reply. I tried to do the same but some error is coming. please see the attachment where is the problem. actually i have not the good knowledge of excel.

    regards
    Noorullah
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,618

    Re: I want to exclude blank cell in index match formula with multiple criteria

    I will look at your workbook now.
    Does it have to be in Excel 2003? In Excel 2007 and 2010 it is possible to use the Table feature. It will automatically expand ranges to include new data.

  8. #8
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,618

    Re: I want to exclude blank cell in index match formula with multiple criteria

    Fixed it. You're doing good, you got it almost right. I had some trouble getting the AMOUNT formula to work. To make it easier to read I renamed your sheet (Datasheet) so it can not be confused with the range Data.

    The second report sheet you can just delete if you want, again it was just for testing.

    I see now by the way it is setup that the Table feature of Excel 2010 wouldn't help much. As long as you insert rows and columns within the data range then it will ofcourse update automatically regardless.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-07-2013
    Location
    uae
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: I want to exclude blank cell in index match formula with multiple criteria

    Thank you very much. Really its very helpful for me.thanks a lot again.

  10. #10
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,618

    Re: I want to exclude blank cell in index match formula with multiple criteria


    -------

  11. #11
    Registered User
    Join Date
    09-07-2013
    Location
    uae
    MS-Off Ver
    Excel 2003
    Posts
    9

    Thumbs up Re: I want to exclude blank cell in index match formula with multiple criteria

    Dear Sir,
    I have the sales data. the problem with the formula is that it is calculating very late. First i used with sumproduct then i used sumif but the result it same. So i request you to please provide a best formula that can quickly calculate the data.

    regards
    Noorullah
    Attached Files Attached Files

  12. #12
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: I want to exclude blank cell in index match formula with multiple criteria

    Try to don't use whole column range in your Named ranges..Example for The Named range ="Units", you use this range..

    =Data!$E$3:$E$65536

    Do it smaller..like =Data!$E$3:$E$10000...

    Or use dynamic named ranges..
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

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

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  13. #13
    Registered User
    Join Date
    09-07-2013
    Location
    uae
    MS-Off Ver
    Excel 2003
    Posts
    9

    Thumbs up Re: I want to exclude blank cell in index match formula with multiple criteria

    Thank your very much.
    your are right. I fixed the range to row 50000 then its calculating little faster than earlier but every day i have to paste the data on data sheet to generate the report and that can reach to row 65000, the how can i give the range to row 10000 or row 50000 thousand to calculate faster. there should be another way to calculate faster than earlier sumproduct or sumif formula. please help me

    Thanks & regards
    Noorullah

  14. #14
    Registered User
    Join Date
    09-07-2013
    Location
    uae
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: I want to exclude blank cell in index match formula with multiple criteria

    Sir,
    Another query is that when i change the date range to see the whole month report or any particular date report, calculating slow. how to delete the name ran

+ 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. Index/ Match Formula, How to return blank cell as a blank not 0
    By MDResearcher in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-24-2016, 08:40 PM
  2. Excel 2007 : Index, Match, Large Formula: Multiple Criteria, Multiple Ranges
    By SimpleJack in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-19-2013, 08:54 AM
  3. Excel 2007 : Index Match Multiple Criteria Formula
    By paddyboy in forum Excel General
    Replies: 17
    Last Post: 03-17-2012, 12:01 AM
  4. Replies: 0
    Last Post: 03-02-2012, 11:16 AM
  5. Index/Match formula with multiple criteria
    By dta1984 in forum Excel General
    Replies: 7
    Last Post: 11-21-2011, 01:29 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