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

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

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. ## 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?

3. ## 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

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

Originally Posted by bebo021999
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.

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

#### 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