+ Reply to Thread
Results 1 to 3 of 3

SUMPRODUCT formula with COUNTIF not working

Hybrid View

Trueman_86 SUMPRODUCT formula with... 07-15-2010, 04:07 AM
DonkeyOte Re: SUMPRODUCT formula with... 07-15-2010, 04:17 AM
Trueman_86 Re: SUMPRODUCT formula with... 07-15-2010, 06:58 AM
  1. #1
    Registered User
    Join Date
    06-30-2010
    Location
    Durham
    MS-Off Ver
    Excel 2003
    Posts
    24

    SUMPRODUCT formula with COUNTIF not working

    What is wrong with this formula?

    =SUMPRODUCT(--(Data!D6:D379=C15),--(Data!A6:A379="NVQ - Direct - ESF Enhanced"),--COUNTIF(Data!O6:O379,"*LATE*"))

    I can get it to work for :

    =SUMPRODUCT(--(Data!D6:D379=C15),--(Data!A6:A379="NVQ - Direct - ESF Enhanced"))

    However I need it to also add the number of cells within that range containing the word "LATE". There might be some words before or after the word, however I still need to count the cells that contain this one word.

    Any Ideas?

    I am unable to post the spreadsheet. sorry.
    Last edited by Trueman_86; 07-15-2010 at 06:57 AM.

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

    Re: SUMPRODUCT formula with COUNTIF not working

    Perhaps:

    =SUMPRODUCT(--(Data!D6:D379=C15),--(Data!A6:A379="NVQ - Direct - ESF Enhanced"),--ISNUMBER(SEARCH("LATE",Data!O6:O379)))

    (change SEARCH to FIND if it is to be case sensitive)

  3. #3
    Registered User
    Join Date
    06-30-2010
    Location
    Durham
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: SUMPRODUCT formula with COUNTIF not working

    Thanks Donkey OTE, works perfectly. A*

+ 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