|
|
Hi Aladin
thanks for this, makes more sense now
Cheers
JulieD
"Aladin Akyurek" wrote in
message ...
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
|