CODEWITHSUNDEEP
excelexcel-formulacellcountif
Alan Tingey
Alan Tingey

Reputation: 961

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

Upvotes: 2

Views: 185

Answers (4)

Bam
Bam

Reputation: 585

=LET(
    data, A2:D25,
    p, CHOOSECOLS(data, 1),
    d, CHOOSECOLS(data, 2),
    s, CHOOSECOLS(data, 4),
    one, MAP(
        UNIQUE(p),
        LAMBDA(m, IFERROR(ROWS(UNIQUE(FILTER(d, (p = m) * s))), 0))),
    two, HSTACK(UNIQUE(p), one),
    two
)

enter image description here

Upvotes: 0

DjC
DjC

Reputation: 1282

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!

Upvotes: 2

P.b
P.b

Reputation: 11468

Using MMULT (twice) you could spill it in one go:

=LET(u,UNIQUE(A2:B25),
     p,TAKE(u,,1),
     m,MMULT((TOROW(A2:A25)=p)*(TOROW(B2:B25)=INDEX(u,,2)),N(D2:D25>0)),
     q,UNIQUE(p),
HSTACK(q,MMULT(N(TOROW(p)=q),N(m>0))))

Upvotes: 0

Black cat
Black cat

Reputation: 6177

Put this formula in I1 and press CTRL-SHIFT-ENTER (array formula)

=SUM(IF(UNIQUE(IF(IF(A$2:A$25=$H1,1,0)*D$2:D$25>0,B$2:B$25,0))>0,1,0))

Drag to the end of column H, where the Product names are.

Upvotes: 1

Related Questions