Solved

Sum/IF/Frequency Function with External Criteria

Posted on 2011-02-24
8
692 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

837 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