LinkBack URL
About LinkBacks
Hi all gurus,
trying to get a rank formula in the attached. The rank formula should be based on same store number in column D and should only rank those sales $ for that store. Any advice?
Ta
Hi all gurus,
trying to get a rank formula in the attached. The rank formula should be based on same store number in column D and should only rank those sales $ for that store. Any advice?
Ta
I got my crystal ball out, but even it couldn't read the attachment - did you use invisible type???
I'm thinking along the lines of RANK and IF combined, but could do with seeing the data.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Oops. sorry.
now with the visible data.
S
Try
F3=1+SUMPRODUCT(($D$3:$D$6=D3)*($E$3:$E$6>E3)) and drag down!!!
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Try this...
Entered in F3 and copied down:
=SUMPRODUCT(--(D$3:D$6=D3),--(E3<E$3:E$6))+1
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks guys. if there is one more condition to this, is there a quick formula to run? In column F, each store has "Include" & "Exclude" as a flag. I only want to rank if the flag is "Include"
Desired outcome in column G.
Thank you.
Like this...
=IF(F3="Exclude","",SUMPRODUCT(--(D$3:D$17=D3),--(E3<E$3:E$17),--(F$3:F$17="Include"))+1)
or
=IF(F4="Exclude","",1+SUMPRODUCT(($D$3:$D$17&$F$3:$F$17=$D4&$F4)*($E$3:$E$17>$E4)))
THanks guys, what if the sales column is in % (not $).
Or try this ...
=IF(F3="Exclude","",COUNTIFS(D$3:D$17,D3,E$3:E$17,">"&E3,F$3:F$17,"Include")+1)
It will work whatever you have in numerical value, I thinkOriginally Posted by shrijan
Mr. Biff Can you confirm us it can work or misbehave??
Yes, it should work on any numeric value.
Thanks Mr. Biff
-------------
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks