Countif with or statement

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
187
Office Version
  1. 365
Platform
  1. Windows
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.

fc8f40_5a949a3b4ef14c60a76c9cba0313fafd~mv2.png



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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
 
Upvote 0
I'm not sure I understand the calculation language, but it works perfectly.

Thank you so much for your help!
 
Upvote 0
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! :cool:
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top