hello
i want to caunt unique values from a array (column c) but only where column F have the value "z569pon" and column G have the value "startet"
this formula count all uinque from the array =SUM(IF(FREQUENCY(B3:B2000;B3:B2000)>0;1))
and this =COUNTIFS(F3:F2000;"z569pon";G3:G2000;"startet") count all not the only the unique
is it possiblie to combine thise to formula?
You can use either:
or (better)=SUMPRODUCT((($F$3:$F$2000="z569pcn")*($G$3:$G$2000="startet"))/COUNTIFS($B$3:$B$2000;$B$3:$B$2000&"";$F$3:$F$2000;$F$3:$F$2000&"";$G$3:$G$2000;$G$3:$G$2000&"")) confirmed with Enter
=SUM(IF(FREQUENCY(IF(($F$3:$F$2000="z569pcn");IF($G$3:$G$2000="startet";MATCH($B$3:$B$2000&"";$B$3:$B$2000&"";0)));ROW($B$3:$B$2000)-ROW($B$3)+1)>0;1)) confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
thanks a lot
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks