How do I combine these two functions?

**worlds&v** · 03-05-2014, 10:28 AM

What I am trying to accomplish is having one collection of summary data at the top of the more detailed information. I would like the summary data to change depending on the filter applied. I can get the data to change with the applied filter with the SUBTOTAL function and can get the data to be summarized counting the way I want. ( I need to count the different numbers that occur without counting duplicates) The problem I am facing is with embedding the SUM/IF/FREQ function bulleted below within a Subtotal function.

• =(SUM(IF(FREQUENCY(F40:F60,F40:F60)>0,1)))
o counts the different numbers that occur without duplicates
• =SUBTOTAL(3,F24:F60)
o I am using to count only what is filtered

I relize I can not embed the count function I need instead of the prexisting 1-9 options given for SUBTOTAL but is there another alternative to achieving the same effect?

Here is an example of my data: example of CR.png

**hemesh** · 03-05-2014, 01:03 PM

Try Below
=(SUM(--(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(F40:F60,ROW(F40:F60)-MIN(ROW(F40:F60))+1,,1)),F40:F60),IF(SUBTOTAL(3,OFFSET(F40:F60,ROW(F40:F60)-MIN(ROW(F40:F60))+1,,1)),F40:F60))>0,1))))
Array (Control Shift and Enter) entered not simple enter

**worlds&v** · 03-05-2014, 02:32 PM

Thank you Hemesh!

This is really close I think. Some of the counts are correct but on others they are off by one (both 1 short or 1 too many)

**Tony Valko** · 03-05-2014, 03:33 PM

In other words...

You want to count the unique numbers in the filtered (or unfiltered) range F40:F60?

If that's what you want to do try this array formula**:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(F40,ROW(F40:F60)-ROW(F40),0)),F40:F60),F40:F60),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**worlds&v** · 03-06-2014, 10:20 AM

YES! Perfect! THANK YOU!!!

**Tony Valko** · 03-06-2014, 10:22 AM

You're welcome. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

**worlds&v** · 04-04-2014, 01:29 PM

Is it possible to have this equation also count text?

**Tony Valko** · 04-04-2014, 08:47 PM

Try this...

Array entered**:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A5,ROW(A5:A10)-ROW(A5),0)),MATCH(A5:A10,A5:A10,0)),ROW(A5:A10)-ROW(A5)+1)>0,1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

That will count the unique entries, text or numbers, in the filtered (or unfiltered) range A5:A10.

**Tony Valko** · 04-04-2014, 09:01 PM

If you need it to count TEXT ONLY...

Array entered**:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A5,ROW(A5:A10)-ROW(A5),0)),MATCH(A5:A10,A5:A10,0)),ROW(A5:A10)-ROW(A5)+1)>0,IF(ISTEXT(A5:A10),1)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

How do I combine these two functions?

LinkBack

Thread Tools

Rate This Thread

Display

How do I combine these two functions?

Re: How do I combine these two functions?

Re: How do I combine these two functions?

Re: How do I combine these two functions?

Re: How do I combine these two functions?

Re: How do I combine these two functions?

Re: How do I combine these two functions?

Re: How do I combine these two functions?

Re: How do I combine these two functions?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Possible to Combine These Functions?

Combine 2 functions into 1

combine functions

Combine functions?

How DO I combine these 2 functions?

Bookmarks

Bookmarks

Posting Permissions