Count with OR statement

Hi Καλή Χρονιά.

One way could be this.

In a cell(let's say H1) type the number that you want to count(or create a validation list to choose)

In D1 and copy down put this formula.

=COUNTIF(A1:C1;$H$1) Column D will be a helper and hidden column.

Then use tis formula to get your result.

=COUNTIF(D1:D10;">=1")

Assuming your data is in the range A2:C11.

=SUMPRODUCT(--(MMULT(--(A2:C11=1),{1;1;1})>0))

If you're wanting to count the number of ROWS where each cell contains a 1 then try it like this:

=COUNTIFS(open1,E2,open2,E2,open3,E2)

You really comfused me.

I get(yesterday i think) a reb * from you and in your comment was this.

Thread: Count with OR statement

Worked. Thanks!

What changed?

One way:

=SUMPRODUCT(SIGN((open1=E2)+(open2=E2)+(open3=E2)))

Originally Posted by Tony Valko

Assuming your data is in the range A2:C11.

=SUMPRODUCT(--(MMULT(--(A2:C11=1),{1;1;1})>0))

Just a follow-up to add some info...

The array {1;1;1} correlates to the number of columns in the data range.

A2:C11 = 3 columns thus the array {1;1;1}.

Originally Posted by akotronis

I don't understand what makes you think that something changed. What you proposed worked for me so I thanked you and voted your answer. I just wanted to find a second (third actually after Tony Valko's solution) way of doing that, using the OR statement, as I had tried in the first place. Is something wrong with that?

In the way that you say...the answer is:

NO. There is not something with that..

=SUM(IF((open1=$E$2)+(open2=$E$2)+(open3=$E$2);1;0))

That one will work in you enter it as an array formula.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

=SUM(IF(OR((open1=1);(open2=1);(open3=1));1;0))

That one won't work because the OR function returns a single result. There might be several rows that meet the criteria but the OR function will evaluate down to a single TRUE and the final result of the entire formula will be 1.

Same thing with this one:

=SUMPRODUCT(SIGN(OR((open1=E2);(open2=E2);(open3=E2))))

Let's take a look at very small simplified example...

A...B...C
1...1...1

Open1 = column A
Open2 = column B
Open3 = column C

E2 = 1

This array formula correctly returns 1:

=SUM(IF((open1=$E$2)+(open2=$E$2)+(open3=$E$2);1;0))

open1=$E$2 = TRUE
open2=$E$2 = TRUE
open3=$E$2 = TRUE

So, the result of:

(open1=$E$2)+(open2=$E$2)+(open3=$E$2) = 3

The IF function evaluates the number 3 as a TRUE logical value. In Excel logical tests typically evaluate to TRUE or FALSE but some functions like the IF function will also evaluate ANY number other than 0 as TRUE and 0 as FALSE.

So we have:

SUM(IF(TRUE,1))

SUM(1) = 1

The other formula:

=SUM(((open1=$E2)+(open2=$E2)+(open3=$E2))*1)

Evaluates to:

open1=$E2 = TRUE
open2=$E2 = TRUE
open3=$E2 = TRUE

(open1=$E2)+(open2=$E2)+(open3=$E2) = 3

SUM((3*1)) = 3

SUM(3) = 3

The best way to see how a formula works is to use the formula auditing tool, evaluate formula.

Select a cell that contains a formula
Goto the Formulas tab
Formula Auditing
Evaluate Formula
Repeatedly clicking the Evaluate button will step through the formula evaluation process until Excel arrives at the final result.

I use this quite often so I've put this button on one of my toolbars.

One thing to do if you use this tool, use it on small samples. You don't want to use it with a formula that references entire columns. After you've tried it few times the reason for this will be obvious!

You're welcome. Thanks for the feedback!