Julie,

Suppose we have

{"Item","Location","Qty";"a","loc1",58;"b","loc1", 71;"a","loc2",70;"a","loc1",39;"b","loc2",73;"c"," loc1",65;"c","loc1",63;"b","loc2",54}

in A1:C9 and we want a summary count per item and per location.

Instead of invoking a formula with SumProduct or Count/If or Sum/If for
multi-conditional counting, we can concatenate Item and Location and
invoke a SumIf formula instead:

D2, copied down:

=A2&","&B2

Let F3:F5 house: {"a";"b";"c"}, the items of interest.

Let G2:H2 house: {"loc1","loc2"}

G3, copied across then down:

=SUMIF($D$2:$D$9,$F3&","&G$2,$C$2:$C$9)

would give us the multi-conditional counts we need.

This set up trades off cell space (memory) against speed (time).



JulieD Wrote:
Hi Aladin

could you please provide an example of what you mean by
"..... one should try to reduce 2 or more conditions to a single
condition
by concatenating them ...."

Cheers
JulieD

"Aladin Akyurek" wrote
in
message ...

Pierre Leclerc Wrote:
...

When you discover the SUMPRODUCT formula, you can forget about
COUNTIF
and SUMIF.

...

That's non-sense. Whenever a single condition involved, one should
invoke CountIf for counting and SumIf for summing. Moreover, if
efficiency (speed) is a concern, one should try to reduce 2 or more
conditions to a single condition by concatenating them and invoke a
CountIf or SumIf formula or a SumProduct or an array formula using
Count or Sum with a lesser number of conditionals.

Also when you discover INDEX/MATCH you can replace VLOOKUP,
HLOOKUP
and LOOKUP.
...

The generality of INDEX/MATCH is no reason to dispense with VLOOKUP
and
HLOOKUP. And there are classes of lookup problems Index/Match cannot
solve, but LOOKUP can.


--
Aladin Akyurek

------------------------------------------------------------------------
Aladin Akyurek's Profile:
http://www.excelforum.com/member.php...fo&userid=4165
View this thread:
http://www.excelforum.com/showthread...hreadid=319150



--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=319150