Hi all,
It is from homework file in vlookup week, "getting the second matching value" article at chandoo website.
I am having difficulty wrapping my head around this formula:
=COUNTIF(INDIRECT(“C5:C”&SUMPRODUCT((MAX((C5:C17=”John”)*(E5:E17))=((C5:C17=”John”)*(E5:E17)))*1,ROW(C5:C17))),”John”)
Could someone please explain this…..
I tried to go different way: through MATCH function to determine the position of John’s max sale. But all it gives to me is 13, instead of 4. If I have an following array :
{0;1088;0;0;0;1540;0;0;0;726;0;0;2682}, how can I make it look like this {1088;1540;726;2682}, in other words how can I get rid of zeros(false) and keep the order of non-zero(true) values.
My formula was like: =match(max((salesman=”john”)*(sales));((salesman=”john”)*(sales));0)
Thank you.
It is from homework file in vlookup week, "getting the second matching value" article at chandoo website.
I am having difficulty wrapping my head around this formula:
=COUNTIF(INDIRECT(“C5:C”&SUMPRODUCT((MAX((C5:C17=”John”)*(E5:E17))=((C5:C17=”John”)*(E5:E17)))*1,ROW(C5:C17))),”John”)
Could someone please explain this…..
I tried to go different way: through MATCH function to determine the position of John’s max sale. But all it gives to me is 13, instead of 4. If I have an following array :
{0;1088;0;0;0;1540;0;0;0;726;0;0;2682}, how can I make it look like this {1088;1540;726;2682}, in other words how can I get rid of zeros(false) and keep the order of non-zero(true) values.
My formula was like: =match(max((salesman=”john”)*(sales));((salesman=”john”)*(sales));0)
Thank you.
