Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: [Solved] Formulas: Compare two data columns ignoring blanks

  1. #1
    Join Date
    11th November 2003
    Posts
    114
    Hi everyone,

    I have two columns A and B where a value in A must be less or equal than a corresponding entry in B.

    Using the following formula to check for errors:
    =sumproduct((A4:A11>B4:B11)*1)

    However, there might be blanks in B but value in A. I don't want to count these occurences.
    How can I achieve that?

    Thank you,
    Greg

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,433
    Try
    =SUMPRODUCT((A4:A11>B4:B11)*(NOT(ISBLANK(B4:B11))))

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    11th November 2003
    Posts
    114
    Great, this is it.

    Thank you, Derk

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Average Ignoring Blanks & Zeros
    By Addy in forum EXCEL HELP
    Replies: 3
    Last Post: July 12th, 2007, 18:53
  2. Compare Formulas Ignoring Range References
    By Malhar in forum EXCEL HELP
    Replies: 6
    Last Post: December 21st, 2006, 17:41
  3. Average Range Ignoring Blanks
    By sweather in forum EXCEL HELP
    Replies: 7
    Last Post: October 29th, 2006, 12:33
  4. AVERAGE fx ignoring zero value (not blanks)
    By Pixyjohn in forum EXCEL HELP
    Replies: 4
    Last Post: July 19th, 2005, 07:37
  5. Replies: 5
    Last Post: August 9th, 2003, 00:52

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
  •  
porno