Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sum/IF/Frequency Function with External Criteria

Posted on 2011-02-24
8
Medium Priority
?
704 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: 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 2000 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

730 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