Link to home
Start Free TrialLog in
Avatar of avgplusguy
avgplusguyFlag for United States of America

asked on

excel unique value 2 conditions

Win XP SVC Pack3   XP 2007 SVC 2  uniquevalue2conditions.xls

=AVERAGEIFS('3 Months Data'!$I:$I,'3 Months Data'!$M:$M,2,'3 Months Data'!$P:$P,0)
Tells me average charges column I where DOW column M = 2 (Monday) and not a holiday or a special day column P = 0

I want average number of customers on Monday
=SUMIFS('3 Months Data'!$Q:$Q,'3 Months Data'!$M:$M,2,'3 Months Data'!$P:$P,0)
Tells me total numbers of Monday customers Column Q = Cases 1 per line   867
{=SUM(IF(FREQUENCY('3 Months Data'!$B:$B,'3 Months Data'!$B:$B)>0,1))}
Tells me number of unique dates, but not number of unique mondays  88

I was hoping this would tell me number of unique mondays but it shows 0
{=SUM(IF(AND(FREQUENCY('3 Months Data'!$B:$B,'3 Months Data'!$B:$B)>0,FREQUENCY('3 Months Data'!$M:$M,'3 Months Data'!$M:$M)=2),1))  }  I am looking for the number 10

Then I could divide formula one by formula two and get that there are 86.7 average customers on a Monday.  I am editing it and saving with Cntrl+Shift+Enter, but I am an arrray beginner.  This way is most likely the long way, but I can explain it to the boss
UniqueValue2conditions.xls
ASKER CERTIFIED SOLUTION
Avatar of NBVC
NBVC
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of avgplusguy

ASKER

December 24th and December 31 are special days with ones in column p and need to be excluded bringing total to 10. I could limit column B to 15,998 and the holidays / special days to 30 staying under the magical 32 and 16,000 numbers
works so NB_VC is gettting some points
{=SUM(IF(FREQUENCY(IF('3 Months Data'!$M$2:$M$15998=2,IF('3 Months Data'!$P$2:$P$15998=0,IF('3 Months Data'!$B$2:$B$15998<>"",MATCH('3 Months Data'!$B$2:$B$15998,'3 Months Data'!$B$2:$B$15998,0)))),ROW('3 Months Data'!$B$2:$B$15998)-ROW('3 Months Data'!B1)+1),1))}


does not work
=SUM(IF(FREQUENCY('3 Months Data'!$M$2:$M$15998=2,IF('3 Months Data'!$P$2:$P$15998=0,IF('3 Months Data'!$B$2:$B$15998<>"",))),1))}

I need something closer to B instead of A that works
Boss gets concerned everytime he finds a match in one of my formulas.
Why is your boss concerned when it works?
Put me on right track.  Thank you