Results 1 to 14 of 14

sumproduct with date criteria and blanks in array

Threaded View

  1. #1
    Forum Contributor
    Join Date
    11-18-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    137

    sumproduct with date criteria and blanks in array

    this is a bit of an odd one. in the attached sheet I have a formula in column D which is working out the following

    Count how many dates in column A on sheet 2 match the month and year in the row on sheet 1 - There are 88 matches - However there are only 86 that have a corresponding number in column B
    For those that match add up the numbers in column B on sheet 2 - this equals 1272
    Divide this total by the number that meet the specified month and year criteria to give an average - this should equal 14.8 but the formula is counting blank cells in column B and including them and giving the answer 14.5 -

    How do I amend the formula in column D on sheet 1 to ignore these blank cells and only work out the average for cells that have matching date criteria AND a corresponding number in column B? I have tried adding the <>"" at the end of the array in question but it gives value of 0 - see column E on sheet 1

    Any help appreciated!
    Thanks
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. List Array Formula with Criteria skip blanks
    By hjforever83 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-24-2020, 01:05 PM
  2. [SOLVED] Sumproduct when 1 array has multiple criteria
    By mark.c in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-14-2019, 01:24 PM
  3. [SOLVED] SUMPRODUCT(SUMIF(....)) where criteria is range of cells with blanks in between
    By dluhut in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-23-2018, 04:55 PM
  4. SUMPRODUCT or SUMIF using an array for criteria
    By rarascon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-24-2013, 03:14 PM
  5. [SOLVED] Sumproduct - 2 criteria with different array sizes
    By Lacaycer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-03-2012, 02:24 PM
  6. Replies: 3
    Last Post: 01-07-2012, 02:51 AM
  7. Replies: 5
    Last Post: 06-14-2006, 07:10 AM

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