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

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
  • 3
  • 2
1 Solution
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.
avgplusguyAuthor 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
avgplusguyAuthor 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.
Why is your boss concerned when it works?
avgplusguyAuthor Commented:
Put me on right track.  Thank you
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now