I am looking to use SUMPRODUCT on a two-column analysis to return a count of unique sessions in column B, on a given date (column A).
How would I be able to do this?
My first idea was to concatenate column A and column B in column C, and by doing so I could count all unique sessions in total, BUT I couuldn't seem to be able to take just one days' worth of sessions.
=SUMPRODUCT(((C5:C14<>"")/COUNTIF(C5:C14,C5:C14&"")))
If you change your first condition such that it checks the date you should find you get what you want...
where C1 holds specific date of interest... and C5 etc are a concatenation of A&BCode:=SUMPRODUCT(--($A$5:$A$14=$C$1),1/COUNTIF($C$5:$C$14,$C$5:$C$14&""))
you could use a FREQUENCY Array if you prefer to avoid use of C
Code:=SUM(IF(FREQUENCY(IF($A$5:$A$14=$C$1,MATCH($B$5:$B$14,$B$5:$B$14,0)),ROW($B$5:$B$14)-ROW($B$5)+1)>0,1)) confirmed with CTRL + SHIFT + ENTER
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks