Count Using Nested "COUNTIF" Statements

[Greg]

Hi,

Problem, I have multiple conditions that need to be met
before it can count all the shipments per day.

Ex. Input:

Date ESOC Priority Process Time (Day)
07/14/03 N 1 0
07/14/03 N 2 1
07/15/03 Y 1 1

Ex. Output:

NON-ESOC (N)
Date No. Ship Priority 1 Priority 2 Priority 3
<= 1 Day <= 2 Days <= 3 Days
07/14/03 2 1 1
"

ESOC (Y)
Date No. Ship Priority 1 Priority 2
<= 24 Hrs <= 48 Hrs
07/15/03 1 1
"

I used the following formulas but the correct value does
not return for each category.

=COUNT(IF($A$3:$A$1202,"7/7/2003"),IF($H$3:$H$1202,"1"),IF
($G$3:$G$1202,"1"))

This formula is to count all Non-ESOC shipments, occuring
on July 7, 2003, Priority 1, and less than 1 day. I also
have a similar formula for the ESOC shipments. The actual
value returned is null.

Does anyone have any suggestions on the proper way to
count the number of occurrences with multiple conditions?
Any help on this problem would be greatly appreciated.

Greg

 

[Bob Phillips]

Greg,

The simplest way (IMO) is to use SUMPRODUCT. This would be used

=SUMPRODUCT((range1=condition1)*(range2=condition2)*...(rangen=conditionnn))

Just ensure that the ranges are the same size.

If you want to sum with these multiple conditions, just tack the range to be
summed at the end

=SUMPRODUCT((range1=condition1)*(range2=condition2)*...(rangen=conditionnn),
(rangetosum))

 

[Greg]

Thanks! I'll try that.

G

-----Original Message-----
Greg,

The simplest way (IMO) is to use SUMPRODUCT. This would be used

=SUMPRODUCT((range1=condition1)*(range2=condition2)*... (rangen=conditionnn))

Just ensure that the ranges are the same size.

If you want to sum with these multiple conditions, just tack the range to be
summed at the end

=SUMPRODUCT((range1=condition1)*(range2=condition2)*... (rangen=conditionnn),
(rangetosum))

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks





.