• 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.

Need a COUNT formula with multiple AND and OR criteria

Grumpy88

Grumpy88

Member
Hi.


I need a formula that will count the number of records in a table that simultaneously complies with two different compulsory as well as either one of two optional criteria. In other words, it must count the number of records in which the Product ="Endowment" AND the Branch ="Cape Town", but also in which the Marketer ="Smith" OR ="Jones".


I have seen an array formula that covers one AND plus one out of two OR criteria, but I haven't been able to convert it to address two AND and one of two OR criteria.


Thanks!
 
Grumpy88


Assuming Endowment is in Column A, Branch is in Column B and Name is in Column C


You can use either:


Code: 
=COUNTIFS(A2:A10,"Endowment",B2:B10,"Cape Town",C2:C10,"Jones")+COUNTIFS(A2:A10,"Endowment",B2:B10,"Cape Town",C2:C10,"Smith")


or


=SUMPRODUCT((A2:A10="Endowment")*(B2:B10="Cape Town")*(OR(C2:C10="Smith",C2:C10="Jones")))
 
Assuming:

Product is in Column A

Branch is in Column B

Marketer is in Column C.


Following formula approaches shall work:

=SUMPRODUCT(--($A$2:$A$11="Endowment"),--($B$2:$B$11="Cape Town"),(($C$2:$C$11="Smith")+($C$2:$C$11="Jones")))


=SUMPRODUCT(($A$2:$A$11="Endowment")*($B$2:$B$11="Cape Town")*ISNUMBER(FIND({"Smith","Jones"},C2:C11,1)))
 
Thanks guys. That half-works, in as far as the first running of the formula produces correct results. However, then subsequently changing the marketers doesn't change the formula result thereafter.


Perhaps I should've added that for my Marketers, I have a dropdown data verification list on which they all appear. I thus don't want to physically edit the formula by typing in the marketer's name each time, but rather have two cells with the identical dropdown lists attached. The idea is then just to select a marketer's name from the list in each cell, and the formula should then pick up the name being displayed in the two cells and count all of the number of records that include either one (or both) of those two marketers.


These formulae at the moment don't seem to pick up the dropdown list contents too well, to the point that once I've selected a Product and I then change the two dropdown Marketer names as well, the formula doesn't change with it - even if I select two different marketers who have never sold that product (as a test), the formula continues to reflect the initial result from the original two marketers selected.
 
Have you adjusted those cell references in the formulae supplied? If No, then please adjust them

e.g.

=SUMPRODUCT(--($A$2:$A$11="Endowment"),--($B$2:$B$11="Cape Town"),(($C$2:$C$11=D2)+($C$2:$C$11=E2)))

where D2 and E2 hold marketer's name.


However, if you've already done that and then you are not being able to see changes then it'd mean the worksheet's calculation mode has been set to manual which needs to be automatic.
 
Thanks Shrivallabha.


That seems to work a treat!! I've never seen two minus signs/dashes together like that in a formula before, but it certainly seems to do the trick whatever it means!


Much obliged, indeed.
 
Copied from another thread:

Chandoo says:

November 10, 2009 at 11:42 am

@Cyril .. good point. the double hyphen is converting a list of boolean (true, false) values to ZEROs and ONEs. Each hyphen acts as a negation. When you negate something, excel converts the underlying values to numbers and then reverses the SIGN. So TRUEs become -1s and FALSEs become 0s. The second negation reverses this and leaves just numbers thus allowing sumproduct to actually multiply instead of throwing an error.
Click to expand...
 
Thanks for kicking in Luke M.


There are few more write ups in case of SUMPRODUCT and I'd like to share them with you.

http://www.mrexcel.com/forum/showthread.php?t=56778

http://www.vbaexpress.com/forum/forumdisplay.php?f=98
 
Thanks guys.


You are all awesome. This is definitely the best site I've come across for obtaining Excel assistance (and certainly the quickest too!)
 
Can I please tack on an extra issue here?


As I have my Marketers in a data validation-obtained dropdown list in cells D2 and E2 explained above, is there a formula-based way to tweak this so that if I want to see the data for all Marketers, rather than for one or two specifically selected from the dropdown lists in D2 and E2, I can do so (in D2 for instance) by just not selecting anyone (or by pressing Delete to clear the previous selection)?


At the moment as soon as I clear D2 (and E2), the formula resets the results to zero because the source cell is then empty. Ideally an "All" selection on the dropdown list would be best, but because the list content comes from data validation of the entries within the source table, there is no "All" item in the table's Marketers column.


Thanks.
 
Grumpy88


I think you will be advised by others to start a new post as it is a new topic. If a drop down question is answered here then others would not know about it because of the origanal title to your origanal post and for the same reason those that could help would not because they would not read the origanal post
 
Thanx BobHC


Grumpy88, BobHC comments are right.

There are 2 Main reasons for starting new post

1. It Makes Searches simpler

2. Often once people have seen a post they don't revisit it if they have no interest in it

So it's in your best interests to start new posts and hence get quicker responses
 
You must log in or register to reply here.
Back
Top