Hi all
I need any suggestions on how to solve this problem,
I'm looking for a formula that calculates how many times this condition (D = E) does not happen, since the last time it happened.
The problem came when I include empty rows, the formula does not work correctly.
I attach a small sample for better understanding (sorry my basic english)
Thanks!
ERROR LOOKUP.xlsx
try below
=SUMPRODUCT((D2:D1000<>"")*(E2:E1000=D2:D1000)*(D2:D1000>0))
it will check for empty row at the same time if D2:D2000 is >0 then only it will perform comparison of D2=E2 and so on
In case D2=E2 and both are zero and want to count it as 1 also then use below
=SUMPRODUCT((D2:D1000<>"")*(E2:E1000=D2:D1000))
Last edited by hemesh; 01-30-2016 at 04:36 PM.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
Try
=SUMPRODUCT(--(D2:D1000>0)*(D2:D1000=E2:E1000))
I assume D/E > 0
I dont know why none of the mentioned formulas works ok, I finally used someone suggestion dynamic names to solve the problem, thanks all for your answers!
As per of your attachment all mentioned formulas work OK ! where it is not working can you please let us know
Ok, i attach the table with two sheets, one ko, and one ok
ERROR LOOKUP-solved.xlsx
You may try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.
Remember that an Array Formula is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
=LOOKUP(2,1/D2:D1000,ROW(D2:D1000))-LOOKUP(2,1/IF(D2:D1000<>"",IF(E2:E1000<>"",(D2:D1000=E2:E1000))),ROW(D2:D1000))
Last edited by sktneer; 01-30-2016 at 05:09 PM.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Fantastic!, this one works like a charm!Originally Posted by sktneer
You may try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.
Remember that an Array Formula is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.=LOOKUP(2,1/D2:D1000,ROW(D2:D1000))-LOOKUP(2,1/IF(D2:D1000<>"",IF(E2:E1000<>"",(D2:D1000=E2:E1000))),ROW(D2:D1000))
Thanks sktneer and all for your help
regards
You're welcome Lehoi! Glad it worked for you.
Also thanks for the feedback as well.
then try below
=MAX((D2:D1000<>"")*(ROW(A2:A1000)))-MAX((D2:D1000=E2:E1000)*ROW(E2:E1000)*(E2:E1000<>""))
copy paste above then hold control and shift together and then hit enter to make it array formula
This one works perfect too, thanks hemesh, all of you are great experts.
Hope one day I could have the 1% of your excel knowledge
@ Lehoi ! You are welcome, Thanks for your feedback.
If you are serious about learning excel Try coming here a lot.
More we learn about excel, more it shows us, how less we know about it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks