# 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

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))}

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!!

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.

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

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!

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

You're welcome. (blush)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1