Book1.xlsx
Hi all!!!
Hello Guys!
I have a problem in which I need to count unique/ distinct (i dunno which one), values that do not duplicate and have a sold value greater than 0 for a certain month.
For example on the rows for a particualar month, there is a duplicate item sold, that duplicate would only be counted once as something that has been sold for that month.
I am trying to do this without a helper column.
Thanks alot before hand.
Try this Array Formula. Array Formula requires confirmation with Ctrl+Shift+Enter instead of just Enter.
In G5
and copy down.
=SUM(IF(FREQUENCY(IF($A$4:$A$22=F5,IF($C$4:$C$22>0,MATCH($B$4:$B$22,$B$4:$B$22,0))),ROW($A$4:$A$22)-ROW($A$4)+1),1))
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. Press F2 on that cell and try again.
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.
A different method, also an array formula:
G5: =SUM(IF(FREQUENCY(IF(($A$4:$A$22=F5)*($C$4:$C$22=1),MATCH($B$4:$B$22,$B$4:$B$22,0)),ROW($B$4:$B$22)-ROW($B$4)+1),1))
Spread the love, add to the Rep
"None of us are as smart as all of us."
Awesome !!!!
And one for the total?
Thanks!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks