countifs function

**doowop5000** · 06-18-2010, 03:42 AM

Hi all - I have a COUNTIFS function that I can't seem to get to work. Basically it is looking for 2 criteria (the count from certain division and certain products) between a date range (first and last day of the month). All cells are referenced and formula as follows:

=COUNTIFS($I$3:$I$22,"=$A$5",$J$3:$J$22,"<="&B4,$J$3:$J$22,">="&B3,$K$3:$K$22,OR("=$A$12","=$a$13"))

A5=division
B4=end date
B3=start date
A12= product type1 (A)
A13= product type2 (AA)

I've attached a 07 workbook below (as it's a countifs) and don't know if this could also be done via a sumproduct. In case it affects things at all, I'm referencing cells from other worksheets...

Any help is greatly appreciated as it will save me banging my head on my desk (again).

doowop

**DonkeyOte** · 06-18-2010, 03:58 AM

COUNTIFS is an AND based function, it does not permit OR type conditions.

To use COUNTIFS you would need to combine 2 separate COUNTIFS (1 for each OR condition).

The SUMPRODUCT alternative to which would be either:

B5:
=SUMPRODUCT(--($I$3:$I$22=$A5),--($J$3:$J$22>=B$3),--($J$3:$J$22<=B$4),($K$3:$K$22=$A12)+($K$3:$K$22=$A13))
applied across matrix

or

B5:
=SUMPRODUCT(--($I$3:$I$22=$A5),--($J$3:$J$22>=B$3),--($J$3:$J$22<=B$4),--ISNUMBER(MATCH($K$3:$K$22,$A$12:$A$13,0)))
applied across matrix

Using the MATCH based approach it's easy to adjust the numbers of conditions in the OR by simply adjusting the range, however, the range must be contiguous (eg A12:A13 and not A12,A14 ...)

**doowop5000** · 06-18-2010, 04:06 AM

Superb - can I ask what the precedent "--" does in the formula? Haven't come across this before...

**DonkeyOte** · 06-18-2010, 04:13 AM

The double unary is used to coerce the Booleans to their Integer equivalents.

In the OR by means of addition example (as opposed to Match) you will note we do not use the double unary - this is because the act of addition will coerce the Booleans thus double unary not required.

For a more detailed overview of SUMPRODUCT and indeed coercion see the link in my sig. to Bob Phillips' white paper.