CODEWITHSUNDEEP
excelcountifdate-range
Иван Олександров
Иван Олександров

Reputation: 49

Calculate unique dates from multiple columns

I have three ranges of dates in Columns B7:B38, F7:F38 and K7:K38. Some dates are duplicates and some dates are unique. Some columns contains few values, and some columns are full. Which formula I can use to calculate unique dates value? I find formula that work fine for one range, but can't figure out how to compare multiple ranges

=SUMPRODUCT(1/COUNTIF(B7:B38;B7:B38))

If I use COUNTIFS for multiple ranges

=SUMPRODUCT(1/COUNTIFS(B7:B38;B7:B38;F7:F38;F7:F38;K7:K38;K7:K38))

it calculate only one longest range of values.

Any advices? Thank you!

Upvotes: 0

Views: 136

Answers (1)

Wolfgang Jacques
Wolfgang Jacques

Reputation: 769

Hello Igor Oleksandrov,

I could not manage to do it in one formula but I think I have a solution for you: List the contents of your ranges with two passes of the small function. The Duplicates can be removed with some comparing in the middle.

Excel Screenshot

In the first version of my answer I put the ranges in a name. That might be handy and nice, but luckily the small function can handle multiple ranges when in ().

Upvotes: 1

Related Questions