Hi there -
Can someone please explain how the following formula works? Specifically the &"" part:
SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20&""))
Thanks! It has been driving me nuts!!
-Victoria
Hi there -
Can someone please explain how the following formula works? Specifically the &"" part:
SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20&""))
Thanks! It has been driving me nuts!!
-Victoria
Good afternoon nothingisthis
This is one of those instances where a formula works as a byproduct of how a function works in the background. The SUMPRODUCT bit can also be replaced by SUM and done as an array formula.
When a COUNTIF function has the criteria the same as the range, then each item in the range is used each time, in turn. The "1/" bit simply divides the result of the items by 1, so 5 would become 1/5 (ie a fifth), and each of these is added together.
Now consider this example :
A range contains the following numbers (only two uniques) : 5, 7, 7, 7, 5, 5, 7
There are 3 fives and four sevens. The resulting sum would add together 3 x 1/3 (for the fives - which = 1) and 4 x 1/4 (for the sevens = 1) and add the two together (=2 uniques).
The &"" bit isn't usually needed - if you have a range containing blanks then it stops errors occuring.
HTH
DominicB
Please familiarise yourself with the rules before posting. You can find them here.
Hi Dominic -
That is mostly clear, but why then does a COUNTIF(C9:19,C9:19) result in 2, in the attached?
it is really only doing
COUNTIF(C9:19,C9) it ignores the rest of the cells c10:c19
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
"When a COUNTIF function has the criteria the same as the range, then each item in the range is used each time, in turn. The "1/" bit simply divides the result of the items by 1, so 5 would become 1/5 (ie a fifth), and each of these is added together."
^This part isn't clear either. I understand that 1/ divides the results by 1, but then with your example, I'm imagining: 1/5 + 1/7 + 1/7 + 1/7 + 1/5 + 1/5 + 1/7. This doesn't equal to 2.
I think I'm not understanding how the COUNTIF really works here...
Martin, if that's the case, then we're left with a 2.
If we apply the formula theres: 1/2, then SUMPRODUCT(1/2).
I think I'm not understanding a step.
It's returning the first element of an array formula.
With the formula entered in E9 as it already is, select E9:E19. Then put the cursor in the formula bar, press and hold the Ctrl and Shift keys, and press Enter. You'll see the rest of the results.
To finish mimicing the single formula, in another cell enter =SUMPRODUCT(1/E9:E19)
Last edited by shg; 04-02-2010 at 02:00 PM.
Entia non sunt multiplicanda sine necessitate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks