Hmm... I suspect there's no really nice efficient way of doing this using formulae, one approach might be to use some "helpers" to ease the expensive calc burden... eg:
D2: =":"&$A2&"::"&$B2&"::"&$C2&":"
copied down
E2: =IF(AND($A2=$A1,$B2=$B1,$C2=$C1),0,--((COUNTIF($D$2:$D$16,":"&$A2&"::"&$B2&":*")-COUNTIF($D$2:$D$16,":"&$A2&"::"&$B2&"::"&$C2&":"))=0))
copied down
If then say B19 contains Milk and B20 Bread then:
C19: =SUMIF($D$2:$D$16,"*:"&$B19&":",$E$2:$E$16)
copied to C20
would return 3 & 1 ... if you changed C3 to say Milk from Bread the result for C19 would become 4
Is that something you can work with ?
Bookmarks