• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Counting unique values if other criteria are true

G

GleeUnit

New Member
I need a formula to count unique values in one column in a table ONLY IF a certain value is true in a different column of the same row.


So for instance, I have a column that is "campaign numbers" and another column that is "status"


I need to know how many campaigns have a status of "active" at any given point in time, but the problem is that each campaign number is listed in multiple rows (depending on how many elements a campaign has). Campaign number "6," for example, has four entries, campaign "8" has seven, and all entries for each campaign will have a status of "active."


I hope that makes sense.
 
GleeUnit


Firstly, Welcome to the Chandoo.org forums.


Sumproduct or Countifs


=Sumproduct((Campaign Range= Campaign No)*(Status Range="Active"))

or

=Countifs(Campaign Range, Campaign No, Status Range, "Active")


To be more specific please upload a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Consider A2:A100 is Campaign Numbers, B2:B100 is Status


Try this with CTRL+SHIFT+ENTER


Code: 
=SUM(IF(FREQUENCY(IF(B2:B100="Active",A2:A100),A2:A100),1))


Or with just ENTER


=SUMPRODUCT((B2:B100="Active")*(MATCH(A2:A100&"|"&B2:B100,A2:A100&"|"&B2:B100,0)=ROW(A2:A100)-ROW(A2)+1))
 
You must log in or register to reply here.
Back
Top