Solved

Sum/IF/Frequency Function with External Criteria

Posted on 2011-02-24
8
695 Views
Last Modified: 2012-05-11
Hello Experts.  I need your help.  I've attached a worksheet to illustrate.

I'm using this function:
[=SUM(IF(FREQUENCY(L2:L11,L2:L11)>0,1)*(M2:M11=0))]
First, I want to sum a column of unique numbers.  The first half of this formula works for this.
[=SUM(IF(FREQUENCY(L2:L11,L2:L11)>0,1))]
Then, I want to modify the sum based on external criteria.
[*(M2:M11=0)]
I get an #N/A response.

I know I could count the number of unique values by using a filter.  In this case I want to build a worksheet formula.  Any insight on what I may be doing wrong here?  Or could you suggest another approach? Frequency.xls
0
Comment
Question by:garyrobbins
  • 4
  • 4
8 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 34970766
Hello Gary,

Try this formula with SUMPRODUCT

=SUMPRODUCT((MATCH(L2:L11&"-"&M2:M11,L2:L11&"-"&M2:M11,0)=ROW(L2:L11)-ROW(L2)+1)*(M2:M11=0))

doesn't need "array entering"

regards, barry
0
 

Author Comment

by:garyrobbins
ID: 34981211
Barry,
Thanks for the prompt response and suggestion.  The formula gives a result of 4 instead of desired result of 3!  (See attached worksheet.)  Can you adjust?  Also, could you offer a brief description of how this formula works?

Thanks,
Gary
Frequency-Rev-1.xls
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34982904
Hello Gary,

I didn't notice the result was different from what you suggested it should be....otherwise I would have mentioned it.....but isn't 4 correct? There are 5 rows where column M is zero...and the values in those 5 rows in column L are 1,3,3,7 and 8, i.e. four different numbers - if you still think the answer should be 3 can you explain why.

In my suggested formula the MATCH function matches the concatenation of column L and M against itself, that returns a (relative) row number within the range, so in your example you get

{1;2;3;4;3;4;7;8;9;4}

You can see that for the first 4 rows you get 1,2,3,4......that's because L2&"-"&M2, L3&"-"&M3, L4&"-"&M4 and L5&"-"&M5 are all different....but then notice the next number is 3, that's because in row 6 the two column entry matches row 4, so in short the numbers are sequential if all different but repeat otherwise, so when we compare against this part

ROW(L2:L11)-ROW(L2)+1

which is just

{1;2;3;4;5;6;7;8;9;10}

you only get a match with the first instance of any two column combination, so with that comparison we get this array

{TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}

the 7 TRUES represent the number of different rows, now when you add in the M column criteria you lose 3 of those so the result is 4.

You could also use this "array formula" to get the same result

=SUM(IF(FREQUENCY(IF(M2:M11=0,MATCH(L2:L11,L2:L11,0)),ROW(L2:L11)-ROW(L2)+1),1))

confirmed with CTRL+SHIFT+ENTER

ergards, barry
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:garyrobbins
ID: 34983590
Barry,

I want to sum a column of unique numbers.  The result I need is 3, as noted.

Gary
0
 

Author Comment

by:garyrobbins
ID: 34983708
Barry,

After a closer look I see how you calculated 4 and I calculated 3.  In the original 10 numbers there are 6 that are unique (1,2,3,7,8 and 10).  Only 3 of them are associated with 0 in column M.  You got 4 by using the external criteria (compare with 0) first and then got (1,3,3,7 and 8) -- where only 4 are unique.  That's why I was using my original formula to use the external criteria only after the determining unique values.

Any new thoughts?

Gary
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34983844
OK Gary, I'm probably being a little dim but I still don't see it, does it matter which way round you consider it? You say

In the original 10 numbers there are 6 that are unique (1,2,3,7,8 and 10)

Yes, no problem I agree with that....but then you say

Only 3 of them are associated with 0 in column M

1 has a zero against it in column M and so do both instances of 3 and the single 7 so I suppose those are the three you are counting....but what about 8, there are two 8s, one has a zero against it in M, the other doesn't, why don't you count 8, does every instance of 8 have to have a zero against it to count or is it done on the basis of the first 8 that appears?

regards, barry
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 34984175
...thinking about this some more I assume that you want to look at the first instance of each value in L2:L11 and count that only if it has a 0 in the corresponding row in M2:M11. If that's the case then it's actually a simpler version of the first formula I provided, i.e.

=SUMPRODUCT((MATCH(L2:L11,L2:L11,0)=ROW(L2:L11)-ROW(L2)+1)*(M2:M11=0))

that will give you 3 for your example

regards, barry
0
 

Author Closing Comment

by:garyrobbins
ID: 34984263
That's it!!  You nailed it.

Thank you very much, you made my week.

Gary
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question