Solved

# excel unique value 2 conditions

Posted on 2013-02-01
198 Views
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
Question by:avgplusguy
• 3
• 2

LVL 23

Accepted Solution

NBVC earned 500 total points
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 Comment

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 Comment

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

LVL 23

Expert Comment

Why is your boss concerned when it works?
0

Author Closing Comment

Put me on right track.  Thank you
0

## Featured Post

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …