Solved

Sum/IF/Frequency Function with External Criteria

Posted on 2011-02-24
8
686 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:garyrobbins
Comment Utility
Barry,

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

Gary
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:garyrobbins
Comment Utility
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
Comment Utility
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
Comment Utility
...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
Comment Utility
That's it!!  You nailed it.

Thank you very much, you made my week.

Gary
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

744 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now