excelexcel-formulacellcountif

How can I use countif to return only a count if sum is > 0 but only count it once

As you can see I wish to count how many days each product has been shorted. The amount shorted is not relevant just the amount of days shorted. Salad saw two days shorted, cheese saw no days and Trifle saw 1 day with shorts. I just can't work out the formula to put into column "I" to automatically calculate days shorted.

enter image description here

Solution

  • In cell I1, use the following formula, then copy it down:

    =SUM(--(SUMIFS($D$2:$D$25, $A$2:$A$25, H1, $B$2:$B$25, UNIQUE($B$2:$B$25))>0))

    OR, to make it spill down automatically, use:

    =BYROW(H1:H3, LAMBDA(r,
SUM(--(SUMIFS(D2:D25, A2:A25, r, B2:B25, UNIQUE(B2:B25))>0))))

    OR, if cell H1 is already dynamic with =UNIQUE(A2:A25), then use:

    =BYROW(H1#, LAMBDA(r,
SUM(--(SUMIFS(D2:D25, A2:A25, r, B2:B25, UNIQUE(B2:B25))>0))))

    OR, to include everything in one array, use:

    =LET(
list, UNIQUE(A2:A25),
HSTACK(list, BYROW(list, LAMBDA(r,
   SUM(--(SUMIFS(D2:D25, A2:A25, r, B2:B25, UNIQUE(B2:B25))>0))))))

    Cheers!