Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Berkshire, UK
    Posts
    22

    Unanswered: Some help with COUNTIF

    Hi all,

    I have a spreadsheet with several tabs. One is a sheet called "Leads" containing my primary information and the other tabs are reports that reference the "Leads" sheet - the one in question is called "Source Detail".

    I have put the following function in a cell in the "Source Detail" tab

    =countif(Leads!d:d,"Unknown")

    And it works.

    However, I have another factor to incorporate into this function - and I can't work it out.

    I want to return the above 'countif' statement, but only where the "Unknown" in column D sits alongside "Advert" in column C.

    Is there a 'where' statement or similar that will help me achieve this?

    So to recap as best I can, in simple terms:

    I want to count all instances of "Unknown" in column D of the Leads sheet where it is next to "Advert" in column C.

    I hope this makes sense and someone can help me.

    All the best,

    John

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. You have to use SUMPRODUCT. It functions like COUNTIF with multiple conditions, only you can't reference the column as a whole like with COUNTIF.

    =SUMPRODUCT((Leads!$D$2:$D$100="Unknown")*(Leads!$ D$2:$D$100="Advert"))
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2004
    Location
    Berkshire, UK
    Posts
    22
    Thanks Shades.

    That worked perfectly.

    Do you know if you can use wildcards in this function? It didn't seem to work when I just tried it.

    All the best.

    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •