# SUM FREQUENCY formula to count distinct values w/ OR condition

1. ## SUM FREQUENCY formula to count distinct values w/ OR condition

See attached workbook.

Table with named ranges for each column. ID is numeric and may or may not have duplicates. Each condition column has values of 1 or 0 (my actual data set also has some that contain the word "Null" but I'm treating those as 0 and only counting the 1's, so it shouldn't be an issue).

I have a working formulas to all 3 conditions with a 1, or 2 of the 3 conditions with a 1 and return the distinct number of IDs in cells F22, F24 and F26. What I'm needing is formula to calculate the distinct ID's with Cond3=1, and if either Cond1 OR Cond2 = 1. In other words, an "either or" instead of a "both and."

Make sense?
sample A.xlsx  Register To Reply

2. ## Re: SUM FREQUENCY formula to count distinct values w/ OR condition

As it appears you understand the principles of the formula, and how to create arrays.

Use * for AND conditions or + for OR conditions, remembering extra parenthesis where required.

{=SUM(--(FREQUENCY(IF((ID<>"")*(Cond3=1)*((Cond2=1)+(Cond1=1)),MATCH(""&ID,ID&"",0)),ROW(ID)-ROW(\$A\$2)+1)>0))}  Register To Reply

3. ## Re: SUM FREQUENCY formula to count distinct values w/ OR condition

Yes, I understand them, but I'm just forgetful as I haven't worked with them in about a year... ah, the plus sign!!! Of course!! ...thanks!!  Register To Reply

4. ## Re: SUM FREQUENCY formula to count distinct values w/ OR condition

Try this formula. Array-entered.
Formula:  `Please Login or Register  to view this content.`

Edit Oops. Didn't refresh before posting.   Register To Reply

5. ## Re: SUM FREQUENCY formula to count distinct values w/ OR condition Originally Posted by HeyInKy just forgetful as I haven't worked with them in about a year...
That's understandable, I forget things I haven't worked with since breakfast   Register To Reply

6. ## Re: SUM FREQUENCY formula to count distinct values w/ OR condition Originally Posted by FlameRetired Try this formula. Array-entered.
Formula:  `Please Login or Register  to view this content.`

Edit Oops. Didn't refresh before posting. "A" for effort and getting it correct though!!! Better late than never?? ...Thanks!  Register To Reply

7. ## Re: SUM FREQUENCY formula to count distinct values w/ OR condition

You're welcome. (blush)   Register To Reply