multi criteria for countif

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

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.
 
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)
 
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))
 
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.
 
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.
Click to expand...
 
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.
 
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)
Click to expand...
...

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.
 
J.E. McGimpsey said:
True. The question I asked, though, was what *advantage* you saw in
the array-entered SUM vs. SUMPRODUCT().
Click to expand...

It was simply an alternative solution,I never stated it was an
*advantage*.
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.
Click to expand...

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

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.
Click to expand...

As stated before I mearly gave him an alternative solution to the
question he directly asked.If he was unaware of array formulas before he
isn't now.(which isn't a bad thing)
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.
Click to expand...

Different strokes for different folks,I remember I started using array
formulas when I discovered they could cut back on the number of formulas
that needed to go into workbooks.
 
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))
Click to expand...





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 said:
Hi and thanks for the replys but the only way the below would work is by
having the formula in each row.

One way:
....
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
Click to expand...

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?
 
...
...
I can remember about 5 years ago array formulas were quite popular in
these groups,new crowd different ideas now I guess.
Click to expand...
...

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.
 
Harlan said:
...
..
..

Most of the regular respondents now were regular respondents then.
Click to expand...

The only regular posters I remember are T Oglivy , C Pearson and H
Staff from about 97 and 98 that are still active in these groups.
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).
Click to expand...

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.
Click to expand...

True but it never hurts to be exposed to different approaches to
problems.
 
Very quick and dirty search discloses at least:

Alan Beban
Jim Rech
Laurent Longre
Robert Rosenberg
Myrna Larson

Alan Beban
 
Which is why I knew better then to make a list,you forget somebody.(who
in turn lets you know that)Which brings up a question,where has Laurent
Longre been?Havent seen any posts from him in quite some time.
 
Alan Beban said:
Google says 473 posts in the last year, 28 in the past 6 months.
Click to expand...

But how many in English? Restricting a Google Groups search to what Google
believes are English language posts since 1-Jan-2003 gives only 15 threads
(Google hit count measures threads in which the individual posted rather
than total postings) in which he participated.
 
Back
Top