• Status: Solved
• Priority: Medium
• Security: Public
• Views: 255

# 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
0
avgplusguy
• 3
• 2
1 Solution

Commented:
I would suggest you reduce the range sizes for these array formulas to max needed...

I get 12 dates (I filtered column M for 2 to check)

=SUM(IF(FREQUENCY(IF('3 Months Data'!M:M=2,IF('3 Months Data'!B:B<>"",MATCH('3 Months Data'!B:B,'3 Months Data'!B:B,0))),ROW('3 Months Data'!B:B)-ROW('3 Months Data'!B1)+1),1))

cse confirmed after you change range sizes to max needed... not whole columns.
0

Author Commented:
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
0

Author Commented:
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.
0

Commented:
Why is your boss concerned when it works?
0

Author Commented:
Put me on right track.  Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.