Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 708
  • Last Modified:

Sum/IF/Frequency Function with External Criteria

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
garyrobbins
Asked:
garyrobbins
  • 4
  • 4
1 Solution
 
barry houdiniCommented:
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
 
garyrobbinsAuthor Commented:
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
 
barry houdiniCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
garyrobbinsAuthor Commented:
Barry,

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

Gary
0
 
garyrobbinsAuthor Commented:
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
 
barry houdiniCommented:
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
 
barry houdiniCommented:
...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
 
garyrobbinsAuthor Commented:
That's it!!  You nailed it.

Thank you very much, you made my week.

Gary
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now