+ Reply to Thread
Results 1 to 4 of 4

How can I count line entries based on meeting criteria for 2 separate columns?

  1. #1
    Registered User
    Join Date
    04-15-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    2

    How can I count line entries based on meeting criteria for 2 separate columns?

    I've been trying for days to figure this out on my own. My head hurts. Please help me.

    What I need:

    I need the following in a formula on a separate sheet from "Article List":

    Count the number of line entries on sheet "Article List" that have both of the following:

    - in B4:B10000, text = "Unassigned Pre-Publish"
    - in D4:D10000, Month = "4"


    What I have:

    I was able to count the number of entries with "Unassigned Pre-Publish" by using:
    =COUNTIF('Article List'!$B$4:$B$999,"Unassigned Pre-Publish")

    I was able to count the number of entries with the specific month by using:
    =SUMPRODUCT(--(MONTH('Article List'!D4:D10000)=4))

    But I cannot figure out how to get entries that meet both criteria.

    I basically need a breakdown of how many entries are marked as "<specific text>" for the month of <month number>. I have a bunch of other variations I need to filter. So once I have this basic formula then I can start to apply it with my other criteria in all the different cells as needed.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: How can I count line entries based on meeting criteria for 2 separate columns?

    Have you tried:
    =SUMPRODUCT(('Article List'!$B$4:$B$999="Unassigned Pre-Publish")*(MONTH('Article List'!D4:D10000)=4))
    yet?
    Quang PT

  3. #3
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: How can I count line entries based on meeting criteria for 2 separate columns?

    Hello,

    What you can use is =SUMPRODUCT(--(MONTH('Article List'!$D$4:$D$10000)=4)*--('Article List'!$B$4:$B$999="Unassigned Pre-Publish"))
    Basically, the -- added in front of bracket saying return 1 for boolean value "True", 0 for "False". Sumproduct will then add them together, thus if something hits both of your criteria, it will return 1*1 = 1 for each of them, and if it doesn't fit both, it'll be 1*0 = 0, or 0*0 = 0.

    Regards
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  4. #4
    Registered User
    Join Date
    04-15-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: How can I count line entries based on meeting criteria for 2 separate columns?

    Quote Originally Posted by bebo021999 View Post
    Have you tried:
    =SUMPRODUCT(('Article List'!$B$4:$B$999="Unassigned Pre-Publish")*(MONTH('Article List'!D4:D10000)=4))
    yet?
    Thank you! Worked like a charm.
    Much appreciated.

+ 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