+ Reply to Thread
Results 1 to 6 of 6

sum multiple criteria date range

  1. #1
    Registered User
    Join Date
    01-12-2010
    Location
    London, england
    MS-Off Ver
    Excel 2003
    Posts
    4

    sum multiple criteria date range

    Hi,

    I'm trying to put a spreadsheet together to provide a count of how much web inventory I've sold for my site.

    I have two products, banners and buttons.
    I would like to be able to see how many impressions i've sold for each day for banners

    I have a column for start date and another for end date.
    I have a third column showing the number of impressions scheduled
    The fourth column shows the type of product - banner or button

    I've tried using sumproduct but can't seem to get it to work!
    Any help would be gratefully appreciated.

    I've attached a sample below.
    Attached Files Attached Files

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: sum multiple criteria date range

    Do you perhaps mean ?

    Please Login or Register  to view this content.
    If you want to SUM the count values

    Please Login or Register  to view this content.
    or if you need to apportion COUNT (qty) based on days between From and To perhaps:

    Please Login or Register  to view this content.
    none of the above are Array formulae, all can therefore be committed with ENTER as normal.

  3. #3
    Registered User
    Join Date
    01-12-2010
    Location
    London, england
    MS-Off Ver
    Excel 2003
    Posts
    4

    Smile Re: sum multiple criteria date range

    Thanks - the second formula was exactly the one I needed

    thanks so much

  4. #4
    Registered User
    Join Date
    01-12-2010
    Location
    London, england
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: sum multiple criteria date range

    Oops. sorry, one last question.
    My spreadsheet is now returning a #value error.
    I would guess as it's because my named ranges contain some cells that are currently empty. I would guess that these cells that are yet to be completed return the error message. Is there any way of ignoring the empty cells?

    thanks

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: sum multiple criteria date range

    Which formula are you using ?

    #VALUE! errors using the suggested approaches are most likely the result of the named ranges being of differing dimensions... this will happen if you're using COUNTA type construct and some columns contain blanks etc...

    to confirm... what do the following:

    =ROWS(from)
    =ROWS(to)
    =ROWS(type)
    =ROWS(qty)

    each return ?

  6. #6
    Registered User
    Join Date
    01-12-2010
    Location
    London, england
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: sum multiple criteria date range

    it's ok now, i cleared contents of each column and everything works fine - so thanks for your help

+ 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