multi criteria for countif

Hi I am trying to do a multi column countif to return a count if all the
occurrences of the below criteria are met. What I want to do is:

if A1:A20 =1
and if B1:B20 =complete
an if C1:c20 =0
count as 1
everytime the above is true a count of the occurrences displayed if the
above is false there is no count. Is this possible with the "countif" or is
there another way to do it?

Thanks for any help possible.
Steve

One way:

=SUMPRODUCT(--(A1:A20=1),--(B1:B20="complete"),--(C1:C20=0))

the --s are necessary since the individual terms return arrays of
booleans, and SUMPRODUCT() errors on booleans. The double unary
minus converts them into numbers. One could instead multiply the
arrays but the comma form is about 25% faster.


In article ,
"Steve" wrote:

Hi I am trying to do a multi column countif to return a count if all the
occurrences of the below criteria are met. What I want to do is:

if A1:A20 =1
and if B1:B20 =complete
an if C1:c20 =0
count as 1
everytime the above is true a count of the occurrences displayed if the
above is false there is no count. Is this possible with the "countif" or is
there another way to do it?

Thanks for any help possible.
Steve

Just curious: what advantage do you see in using an array-entered
formula that is even slower than using multiplication within a
non-array-entered SUMPRODUCT?

=SUMPRODUCT((A1:A20=1)*(B1:B20="complete")*(C1:C20 =0))

In article , Thomas wrote:

If a few more millaseconds is not a big deal use
=SUM((A1:A20=1)*(B1:B20="complete")*(C1:C20=0)
array entered(cntrl-shift-enter)

Hi Thomas
I feel you are doing J.E.McGimpsey a dis-service. I for
one like to know if a process can be made faster.
I would agree nanoseconds to a novice such as myself
would not really worry me, due to my spreadsheets being
small in size, but from groups such as this one I have
sped up and tidied up my macro to run faster and without
the screen flashing and jumping from sheet to sheet.
Keep the tips and hints coming.
It costs nothing to carry knowledge around with you.

regards Bob Christie

-----Original Message-----
You use the word "slow"& "array" like its a 5 minute
operation.For the
OP data an array formula would suffice just as easily as
sumproduct with
no noticable lag.

"J.E. McGimpsey" wrote:

Just curious: what advantage do you see in using an
array-entered
formula that is even slower than using multiplication
within a
non-array-entered SUMPRODUCT?

=SUMPRODUCT((A1:A20=1)*(B1:B20="complete")*
(C1:C20=0))

In article , Thomas
wrote:

If a few more millaseconds is not a big deal use
=SUM((A1:A20=1)*(B1:B20="complete")*(C1:C20=0)
array entered(cntrl-shift-enter)
.

True. The question I asked, though, was what *advantage* you saw in
the array-entered SUM vs. SUMPRODUCT().

Not trying to start a flame fest here - for the OP, the difference
is, as you said, not worth talking about. Yet you made the
recommendation of an alternative. I was just curious as to why,
since I try to avoid array-entered formulas wherever possible.

OTOH, I had no idea whether the OP gave an example from a workbook
with that one formula, or one with thousands of similar operations,
so it made sense to me to propose the most efficient solution, for
which I gave at least a partial explanation.

FWIW, as someone who's written relatively efficient applications
that took minutes to calculate, a 20% speed improvement catches my
attention. Granted, they should have been done in a database
program, where they would have been very efficient, but that's not
what the client wanted or was willing to pay for.

In article , Thomas wrote:

You use the word "slow"& "array" like its a 5 minute operation.For the
OP data an array formula would suffice just as easily as sumproduct with
no noticable lag.

"Thomas" wrote...
If a few more millaseconds is not a big deal use
=SUM((A1:A20=1)*(B1:B20="complete")*(C1:C20=0)
array entered(cntrl-shift-enter)
...

Even a few for milliseconds nbd. However, the advantage to SUMPRODUCT over SUM
is that the former usually doesn't need to be entered as an array formula, but
the latter must always be in conditional sums. Experience will show you that OPs
will have less trouble using SUMPRODUCT than SUM. IOW, the advantage may not
matter to you, but it will to those you're supposedly trying to help.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

OK.

In article , Thomas wrote:

It was simply an alternative solution,I never stated it was an
*advantage*.

Hi and thanks for the replys but the only way the below would work is by
having the formula in each row.

One way:



=SUMPRODUCT(--(A1:A20=1),--(B1:B20="complete"),--(C1:C20=0))





What I need to be able to do is shown below, sorry for the confusion



SHIFT STATUS VALUE CELL G1
= TOTAL COUNT = 2

Cells Value Cells Value Cells Value

A1 1 B1 COMPLETE C1 23 1, COMPLETE, 0 = TRUE,
COUNT 1

A2 2 B2 C2 36 2, 0 = FALSE,
NO COUNT

A3 1 B3 COMPLETE C3 15 1, COMPLETE, 0 = TRUE,
COUNT 1

A4 1 B4 COMPLETE C4 0 1, COMPLETE, 0 = FALSE,
NO COUNT

A5 3 B5 COMPLETE C5 25 3, COMPLETE, 0 = FALSE,
NO COUNT



What I need is a count of every cell that meets this criteria "A1:A5 = 1 and
B1:B5 = complete and C1:C5 0" and have the total count placed in cell G1



And again thanks for any help anyone can provide

"Steve" wrote in message
...
Hi I am trying to do a multi column countif to return a count if all the
occurrences of the below criteria are met. What I want to do is:

if A1:A20 =1
and if B1:B20 =complete
an if C1:c20 =0
count as 1
everytime the above is true a count of the occurrences displayed if the
above is false there is no count. Is this possible with the "countif" or
is
there another way to do it?

Thanks for any help possible.
Steve

"Steve" wrote...
Hi and thanks for the replys but the only way the below would work is by
having the formula in each row.

One way:

=SUMPRODUCT(--(A1:A20=1),--(B1:B20="complete"),--(C1:C20=0))
....
What I need is a count of every cell that meets this criteria "A1:A5 = 1
and
B1:B5 = complete and C1:C5 0" and have the total count placed in cell G1

Did you try this SUMPRODUCT formula? Change all instances of 20 to 5 and =0
to 0 and it *DOES* calculate what you claim you want.

What do you think SUMPRODUCT does?

"Thomas" wrote...
...
I can remember about 5 years ago array formulas were quite popular in
these groups,new crowd different ideas now I guess.
...

Most of the regular respondents now were regular respondents then.

It's not fashion/fad. It's evolution. Array formulas are still useful, and in
some instances necessary (if you need IF in order to filter out error values,
there's less advantage to SUMPRODUCT becasuse it'd also need to be
array-entered). However, when offering presumably less experienced Excel users
assistance, it's easy to see from the number of follow-ups by OPs (well, you may
need to look through the archives for 2001-2) stating that formulas return
#VALUE! or asking "what's an array formula?" that avoiding array formulas in
newsgroup responses has practical advantage.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.