Multiple Condition CountIf

A

Amanda Guenthner

I need to count the number of times that the cells that match mulit-criteria.
Would a CountIf or an Array work. Tried a DCount, but could not get it to
work. I am trying to determine the following:

A1:A10 = "Active" AND B1:B10 = "X" OR C1:C10 = "X" OR D1:C10 = "X"

A B C D
Active X X
Active X
Not X
Active X
Active X X

Answer should be 4 since I want this mutually exclusive (i.e. X in mulitple
columns only counted once). Sorry if this is explained poorly!

Thanks,

AG
 
A

Amanda Guenthner

Thank you for the quick reply - it worked! I am not familiar with using --,
could you perhaps explain why this is used?
Again - thanks!
 
A

Amanda Guenthner

I have one more question on this issue. How can I revise the formula you
provided to calculated the revenue that fits this criteria.

Specifically I need the summation of the revenue figures (assume E1:E10)
that fit the criteria in your formula. So basically need to figure out how
to stop the count function and replace w/ sum.

Thanks!
 
D

Daniel.M

Hi,

=SUMPRODUCT((A1:A10="Active")*((B1:B10="X")+(C1:C10="X")+(D1:D10="X")>0)*E1:E10)

Regards,

Daniel M.
 
A

Amanda Guenthner

Thanks Daniel! I actually tried the following and it worked too:
=SUMPRODUCT(--(N16:N25="Opportunity in
GBDD"),--((J16:J25="X")+(K16:K25="X")+(L16:L25="X")>0),(T16:T25))

Thank you for your help :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count if condition in two columns 4
How do I get started using the Countif, Countifs option with Excel 2
Countif: Multiple Columns 4
Looking up non-unique data 1
Unique Value Count in 3 Columns Using Search Criteria 0
How to count array with OR condition 4
How do I use countif like sumif ie (range,criteria,count_range) 1
Need Help: Multiple Criteria 3

Top