avgplusguy
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
=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('
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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<>"",MA TCH('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.
{=SUM(IF(FREQUENCY(IF('3 Months Data'!$M$2:$M$15998=2,IF('
does not work
=SUM(IF(FREQUENCY('3 Months Data'!$M$2:$M$15998=2,IF('
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?
ASKER
Put me on right track. Thank you
ASKER