CODEWITHSUNDEEP
excel-formula
HotSauceCoconuts
HotSauceCoconuts

Reputation: 321

COUNTIFS or SUMPRODUCT : Count of non-blank values in range with index criteria matching a string

I am trying to count how many cells have a date value in a range, where the row index matches a given criteria; a string value matching a corresponding value in another column on the same row.

Basically a COUNTIF formula, where the count range is different from the search range.

I believe I almost have the answer but it's quite right:

=SUMPRODUCT(--(tblUK[Content Type]=[@[Content Type]]),tblUK[Completed]<>"")

Edit: The data is actually two different tables, this might clarify:

=SUMPRODUCT(--(tblUK[Content Type]=tblBurndown[@[Content Type]]),tblUK[Completed]<>"")

or without Table formula jargon...

=SUMPRODUCT(--(UK!F:F="Physical",UK!E:E<>"")

Upvotes: 0

Views: 251

Answers (1)

HotSauceCoconuts
HotSauceCoconuts

Reputation: 321

I have come up with an answer using COUNTIFS after all:

=COUNTIFS(tblUK[Completed],">0",tblUK[Content Type],[@[Content Type]])

However, I don't see why SUMPRODUCT won't do the job just as well, would be good to find out why it isn't working.

Edit: I think I've cracked it... just had to do a bit more reading on SUMPRODUCT. The missing element was multiplying the boolean array (first array) by the 2nd array.

=SUMPRODUCT(--(tblUK[Content Type]=[@[Content Type]])*(tblUK[Completed]>DATEVALUE("01/01/1900")))

Upvotes: 0

Related Questions