Multiple conditions with COUNTIF function

  • Thread starter Thread starter Jenny
  • Start date Start date
J

Jenny

I have a large database of work orders. I need to find
the number of entries that are from particular
departments (1st condition) and from a particular month
(2nd condition). My date column is in the format 15-Jun-
04. I am currently using it on Windows XP(at home) but
work only has (Windows 97), which is where it will need
to be eventually run. The database also needs to be
continuous as entries will continue to be put in over a
long period of time.

I have tried the following but I keep coming up with
various errors (different every time):

=SUM(IF(Location="Administration",IF(Date<"1-Jun-04",IF
(Date>"31-Jun-04",1,0)
 
I thrust Location and Data are defined by means of dynamic formulas. If this
is done correctly, the size of Location and that of Data should be equal...

=SUMPRODUCT(--(Location=E2),--(Date>=F2),--(Date<=G2))

where E2 houses a department of interest like Administration, F2 a true date
like 1-Jun-04, and G2 31-Jun-04 or a formula like:

=DATE(YEAR(F2),MONTH(F2)+1,0)
 
Try:

=SUMPRODUCT((A1:A1000="Administration")*(MONTH(B1:B1000)=6)*(YEAR(B1:B1000)=
2004))

Adjust the ranges to suit.
 
You can use an array formula for this


=SUM((Location="Administration")*(Date>=DATE(2004,6,1))*(Date<=DATE(2004,6,3
0))

Have you thought about a pivot table? You could use, e.g., the location as a
column field, the date as a row field, and group the dates by month and
year.
For the data field, use the data or location field again, with the function
COUNT rather than SUM.

A Pivot table will give you the totals for all locations, all months, in a
nice table. BTW, you can't have any blanks if you want to group the dates,
so you'd have to use a named range like

=OFFSET(A1,0,0,COUNTA(A:A),10)

If you were to name that range, say, TheData, you would use TheData as the
source for the pivot table. Then the source will update automatically as you
add new data at the end.
 
Myrna Larson said:
You can use an array formula for this

=SUM((Location="Administration")*(Date>=DATE(2004,6,1))
*(Date<=DATE(2004,6,30))
Click to expand...
....

Given the headache of explaining how to enter array formulas, it's usually
best to avoid them when possible. This is such a situation. For conditional
summing, SUMPRODUCT usually beats array SUM except in terms of formula
length.
 
I agree re the nuisance of explaining array formulas, but an equal nuisance is
the need to explain why, with SUMPRODUCT you have to put the tests in
parentheses* and multiply them, rather than just separating them with commas,
as Help for SUMPRODUCT shows.

*Can't remember right now if this is always required, but I have hit formulas
where they are.
 
Myrna Larson wrote...
I agree re the nuisance of explaining array formulas, but an
equal nuisance is the need to explain why, with SUMPRODUCT
you have to put the tests in parentheses* and multiply them,
rather than just separating them with commas, as Help for
SUMPRODUCT shows.
Click to expand...
...

It's a mystery why it'd be necessary to explain the parenthesize
expressions in

=SUMPRODUCT((RangeA=x)*(RangeB=y))

but not have to explain them in

=SUM((RangeA=x)*(RangeB=y))

since online help for SUM also omits any use of parenthesize
arguments. In conditional counting, both SUM and SUMPRODUCT requir
that boolean array expressions be converted into numbers (1s and 0s)
and that can be done by using them together or separately as arithmeti
operands
 
Hi Myrna

in most cases you can use the ',--' syntax which is quite similar to
the original syntax (of course will will cause the question for the
reason behind the unary operator). Some types of SUMPRODUCT formulas
which won't work with this style but require the '*' syntax:
- if you mix row and column oriented conditions (e.g. text row 1 and
column A)
- if you want to sum more than one column
- if you compare to a cell range and use 'Transpose' within SUMPRODUCT
to do this (instead of an ISNUMBER/MATCH combination)
 
Except my expectation (which is unfulfilled, of course) is that in SUMPRODUCT,
*by definition*, the arrays are going to be multiplied, so in this situation
Excel should be smart enough to convert the TRUE/FALSE to 1/0 without the user
having to use * or -- operations. Maybe in the next version (but I'm not
holding my breath)...
 
Frank Kabel said:
Hi Myrna

in most cases you can use the ',--' syntax which is quite similar to
the original syntax...
Click to expand...

A peculiar association... And I dare to say, a falsehood. The -- coercer
specifically has nothing to do with the original (or native) syntax of
SumProduct, a function which is directed at multiplying numerical ranges:

=SUMPRODUCT(NumRangeA,NumRangeB,...)

Someone (Longre?) has associated the idea of conditionals [ like
Range="v" ], coerced into numbers, as satisfying the numerical objects that
SumProduct requires: probably first in the form of...

=SUMPRODUCT((RangeX|RelationalOperator|Condition) *
(RangeY|RelationalOperator|Condition) * ...)

which is in effect just

=SUMPRODUCT(X)

where X is a single array that results from multiplications. Defining a
conditional as an expression in the form of:
(RangeX|RelationalOperator|Condition), we had...

=SUMPRODUCT(Conditional1*Conditional2*...*RangeToSum)

Text values in RangeToSum, which cause a #VALUE error made a partial return
to SumProduct's native systax:

=SUMPRODUCT(Conditional1*Conditional2*...,RangeToSum)

There was enough people (including myself) that often switched to the full
comma syntax, which is possible by explicitly coercing the conditionals...

=SUMPRODUCT(Conditional1+0,Conditional2+0,...)

=SUMPRODUCT(Conditional1+0,Conditional2+0,RangeToSum)

Some also used *1 instead of +0...

Harlan just introduced another coercer: -- (a pair of unary minuses).

Admittedly, many adopted this new coercer, a state of affairs that advanced
the use of the native comma syntax.

The star syntax, as was often used, is still necessary in SumProduct
formulas that are set up to process vectors and multicolumn/row matrices.
 
Myrna Larson said:
Except my expectation (which is unfulfilled, of course) is that in SUMPRODUCT,
*by definition*, the arrays are going to be multiplied, so in this situation
Excel should be smart enough to convert the TRUE/FALSE to 1/0 without the user
having to use * or -- operations.
Click to expand...

Definitely. And it would also mean a gain in efficiency.
 
Hi Aladin
A peculiar association... And I dare to say, a falsehood. The --
coercer specifically has nothing to do with the original (or native)
syntax of SumProduct, a function which is directed at multiplying
numerical ranges:
Click to expand...

First thanks for the detailed explanantion. I only meant it LOOKS
similar to the original syntax :-)


Frank
 
Myrna Larson wrote...
Except my expectation (which is unfulfilled, of course) is that in
SUMPRODUCT, *by definition*, the arrays are going to be
multiplied, so in this situation Excel should be smart enough to
convert the TRUE/FALSE to 1/0 without the user having to use *
or -- operations. Maybe in the next version (but I'm not
holding my breath)...
Click to expand...
...

Personally, unless DCOUNT/DSUM/etc. are significantly improved,
really hope you're disappointed.

Given A1:C4 containing (underscores used for closest visual layout fo
monotype and proportional fonts - replace w/ spaces)

A__1_____1
A__2____20
B__2___FOO
C__1__4000

SUMIF(A1:A4,">A",C1:C4) returns 4000. To me it's a darn good thing tha
SUMPRODUCT((A1:A4>"A")*(B1:B4>0),C1:C4) also returns 4000.

This only matters for conditional summing, not counting, but PLEAS
don't fubar my multiple condition sums!

And with regard to 'by definition', by definition SUM and PRODUC
respectively sum and multiply their arguments, but SUM({"9"}) an
PRODUCT({"9"}) both return 0 rather than 9, and from online help fo
SUMPRODUCT, "SUMPRODUCT treats array entries that are not numeric as i
they were zeros." Definitions *can* be qualified.

Part of the problem is that the SUMPRODUCT entry in online hel
contains this bit of misinformation: "Array1, array2, array3, ... ar
**2** [emphasis added] to 30 arrays whose components you want t
multiply and then add." Clearly this is wrong and has been wrong fo
over a decade now, but it's also equally clear that Microsoft will n
sooner fix this than they'll release their own Linux distribution
 
Hi, I tried what you said but it just kept coming up with
a number error. This is what I put in:

=SUMPRODUCT((Mechanics!C:C="Administration")*(MONTH
(Mechanics!A:A)=6)*(YEAR(Mechanics!A:A)=
2004))

Am I doing somthing wrong? Or any more ideas.

Thanks
 
Hi, I tried what you said but it didn't seem to work, it
just kept coming up with a number error. This is what I
put in:

{=SUMPRODUCT(--(Sheet2!C:C=Sheet2!D1),--(Mechanics!
A:A>=Sheet2!K6),--(Mechanics!A:A<=Sheet2!L6))}

Am I doing something wrong? Or any other ideas.

Thanks
 
Hi, I tried what you suggested but it keeps coming up
with a number error. This is what I put in:

{=SUM((Mechanics!C:C="Administration")*(Mechanics!
A:A>=DATE(2004,6,1))*(Mechanics!A:A<=DATE(2004,6,30)))}

Am I doing something wrong? Or is there another option?

Thanks
 
Hi
some things :-)
- don't enter this formula as array formula. This is not required. A
simple ENTER will do
- SUMPRODUCT does not accept ranges like C:C. You have to use something
like C1:C1000.

So try
=SUMPRODUCT(--(Sheet2!C1:C1000=Sheet2!D1),--(Mechanics!
A1:A1000>=Sheet2!K6),--(Mechanics!A1:A1000<=Sheet2!L6))}
 
Thanks heaps, its working great!!!
-----Original Message-----
Hi
some things :-)
- don't enter this formula as array formula. This is not required. A
simple ENTER will do
- SUMPRODUCT does not accept ranges like C:C. You have to use something
like C1:C1000.

So try
=SUMPRODUCT(--(Sheet2!C1:C1000=Sheet2!D1),--(Mechanics!
A1:A1000>=Sheet2!K6),--(Mechanics!A1:A1000<=Sheet2!L6))}


--
Regards
Frank Kabel
Frankfurt, Germany



.
Click to expand...
 
Back
Top