+ Reply to Thread
Results 1 to 3 of 3

multiple criteria count

  1. #1
    Registered User
    Join Date
    06-19-2009
    Location
    saskatoon, canada
    MS-Off Ver
    Excel 2003
    Posts
    1

    multiple criteria count

    In my problem I have 2 columns(a2:a351 and b2:b351), each containing data, however some cells are empty. I want to be able to count when each corresponding set of data(ie, a33 and b33) are both non empty cells. So if the entire data was to be all blank, the count should be 0 and if each cell had data in it the count would be 350. Data is on sheet 1, columns b and c. Count is on sheet 2, column c.

    This is the syntax that was recommended to me: line (c2, sheet2)
    =SUMPRODUCT(--Sheet1!C2:C351<>"")--(Sheet1!D2:D351<>"")

    I have been trying to use the count and sum functions in numerous different ways but have had no luck whatsoever. Here are some of the syntax I have been trying.

    =COUNT(IF(A2:A351<>"0",IF(b2:b351<>"0")))
    =SUM(IF(A2:A351<>"",IF(B2:B351<>"",1,0),0))
    =COUNTIF(A2:B351,AND(A2:351<>"",B2:B351<>""))
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    04-01-2009
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    280

    Re: multiple criteria count

    I think the SUMPRODUCT was the correct approach, the format appears to be wrong. Try this:

    =SUMPRODUCT(--(Sheet1!B2:B351<>""),--(Sheet1!C2:C351<>""))

  3. #3
    Forum Contributor kraljb's Avatar
    Join Date
    05-26-2004
    Location
    Illinois
    MS-Off Ver
    2007 (recent change)
    Posts
    256

    Re: multiple criteria count

    Looks like you just need a slight formula correction... Use this for the SUMPRODUCT Formula:

    Please Login or Register  to view this content.
    Hope that helps...
    John
    "I don't know what I don't know"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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