COUNTIF - multiple criteria (excel 2003)

If with Multiple range in Sumproduct won’t get the right results…

Replace the SUMPRODUCT with SUM and enter it as 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.

Hi,

First of all, it's very unlikely that the use of the IF in there is necessary (or even correct).

Secondly, can you explain in words what each of the typical bracketed criteria is trying to achieve? For example, a construction such as:

((Database!$B$2:$B$14=$B$7)+($B$7=""))

which seems to make up the majority of your conditions, is logically quite unusual, and in all probability incorrect, since the second part ($B$7=""), which is either TRUE or FALSE, will be added to the array of returns from the first part (Database!$B$2:$B$14=$B$7) and so could produce returns greater than 1. For example, if Database!$B$2:$B$14 contains

{7;1;5;(blank);6;10;6;(blank);2;4;(blank);10;9}

and $B$7 is blank, then:

((Database!$B$2:$B$14=$B$7)+($B$7=""))

will equate to:

({FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}+TRUE)

which is:

{1;1;1;2;1;1;1;2;1;1;2;1;1}

which may not be at all what you want.

Regards

Me too thought to raise question about this approach ((Database!$B$2:$B$14=$B$7)+($B$7="")) but left that part by saying myself + operates as OR condition in Array Formulas…

"+ operates as OR condition in Array Formulas…"

Only if the two parts are mutually exclusive (and of the same size).

Here, for example, it's quite possible that Database!$B$2:$B$14=$B$7 AND $B$7="" are BOTH true, in which case the "+" is not having the (desired) effect of acting as an OR operator.

Regards

@ XOR LX,

Thanks for the info...

I would like to share a scenario with you for getting better understanding on this…

We will test this with two sheets Database sheet and Current Sheet

In Database sheet just leave B6 & B8 as blank and in B7 type abc
In Current Sheet – Put the below formula and leave the B7 cell as blank

As you are aware it is array formula…
=SUM(IF((Database!$B$6:$B$8=$B$7)+($B$7=""),1,0))

It will get the count of 3. But in data base sheet Two cells only blank so the result should be 2. But its treating the next B7 with or condition and checking that too even though it is not in equal size and getting the count of 3.

Please suggest and correct me if my understanding is wrong…

Perhaps, but you've artificially changed the scenario to return 1s in the case of a TRUE, and in any case I don't think the example you give is applicable generally.

Change it to (as in the OP's example):

=SUMPRODUCT((Database!$B$6:$B$8=$B$7)+($B$7=""))

and see what you get.

Regards

Yes… wantedly I made it in that way to prove the real array formula will treat the + as OR operator lol

In your example the Sumproduct condition is adding the Or condition result to each result and getting the 6 as result like True;True;True+True = True+True;True+True;True+True.

But we are aware Sumproduct and IF with array both will act in a different way instead doing performing the same terminology…

"But we are aware Sumproduct and IF with array both will act in a different way instead doing performing the same terminology…"

Not really in this case. Remember, you arbitrarily added in a return of 1 for the TRUE condition. The actual array replica of that SUMPRODUCT formula would be:

=SUM((Database!$B$6:$B$8=$B$7)+($B$7=""))

which again, in your example would give 5.

But I know what you mean!

But I know what you mean!

Hm… I got your point….

But in OP’s example he used If with Sumproduct combination and I converted it as If with Sum combination as an array and in this case the + will operate as Or only…

So both of us won lets rep each other lol

"lets rep each other lol"

Have done!!

(Don't tell the Moderators though or they'll suspect us of some mutual repping collusion! )

Me too did it

Ha ha ha already they are in the job of moderating each threads lol