+ Reply to Thread
Results 1 to 2 of 2

problem with a countif or sumproduct formula

  1. #1
    Registered User
    Join Date
    10-04-2007
    Posts
    15

    problem with a countif or sumproduct formula

    HI

    trying to work out a formula that looks at a cell, finds the ref in another sheets, checks a column and adds up all the dates that are within 6 months.

    I have tried "SUMPRODUCT((VLOOKUP(A5,Data!A3:G6,7,0)<H3)*1)" where H3 is a date.

    If have tried countif but is does not seem to like having to look for a "lesser than" value if it's a date ?

    Anyhelp would be much appreciated.

    I have uploaded the sheets I'm working on. Very basic at moment.

    thanks

    Mickeyp
    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: problem with a countif or sumproduct formula

    COUNTIF can accept only one criteria, you seemingly have 2 - date & name... pre XL2007 your options are quite limited, SUMPRODUCT (or CSE Array) or use Concatentaion so as to negate need for SUMPRODUCT etc...

    if your data table is relatively small and number of SUMPRODUCTS relatively few then SUMPRODUCT is not a bad option per se, eg:

    H5: =SUMPRODUCT(--(Data!$A$3:$A$92=$A5),--(Data!$G$3:$G$92<H$3),--(Data!$G$3:$G$92>=I$3))
    copied across to J5 and down for rows as required

    NOTE: your data types in column G on DATA should really be consistent, your first entry for Barker. J for ex is a date stored as text, you could negate that with:

    H5: =SUMPRODUCT(--(Data!$A$3:$A$92=$A5),--(0+Data!$G$3:$G$92<H$3),--(0+Data!$G$3:$G$92>=I$3))

    but if G were to contain non-numeric text strings (like a Null or "apple") the Sumproduct would error.

+ 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