Countif with or statement

[melodramatic]

OK, I know this is easy, but it's escaping me today.

I've got a Lottery table for our group, and part of my calculation of winnings distribution is dependent on how many people have played that month. January thru October is easy - if you paid, you played. About November, we start using our winnings credit to pay our monthly play, so it's not as easy to count.

What I need to be able to do is count how many people paid to play in the Lotto (Col D), as well as how many people played using their credit (Col E), but I don't want to double-count those who paid and used some credit.

So, what I want is for cell D4 to give me the calculation of anyone who has either paid or used balance. The total should be 16, but how do I get there?

Thanks so much in advance!

 

[Eric W]

Try:

=SUM(SIGN(MMULT(C8:D23+0,{1;1})))

or

=SUM(SIGN((C8:C23>0)+(D8:D23>0)))

which must be entered with Control+Shift+Enter.

 

[melodramatic]

I'm not sure I understand the calculation language, but it works perfectly.

Thank you so much for your help!

 

[Eric W]

I can see why you might be confused, since neither formula has an OR in it! But OR doesn't work very well with arrays. The first formula essentially adds the 2 values per line together, figures out the sign (which will be 0 or 1) and adds them up. The second one creates a TRUE/FALSE test for each row in C, and each row in D, adds them together, which results in 0, 1, or 2, then does the SUM(SIGN( trick again.

Glad to help!