+ Reply to Thread
Results 1 to 19 of 19

Count unique values in a data range with exemption

  1. #1
    Registered User
    Join Date
    02-19-2013
    Location
    Philippines
    MS-Off Ver
    Excel for Mac 2011
    Posts
    16

    Count unique values in a data range with exemption

    Hi, I'm fairly new in using excel formulas and have encountered a small dilemma. I've searched the net for formulas but nothing returns the result that I'm looking for. Anyway, here's what I want to achieve:

    I want to count how many times the value "Personal" occurred in a column (that can simply be done by a countif formula) BUT I don't want excel to count the cells with the value "Personal" IF on the same row in another column the value "Cancelled" is found.

    Can this be achieved? Thank you

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count unique values in a data range with exemption

    Try something like this.

    =SUMPRODUCT((A1:A1000="Personal")*(B1:B1000="Cancelled"))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,709

    Re: Count unique values in a data range with exemption

    I don't know if this is a feature in your version of Excel. In E2007 and E2010 you can use the CountIfs function.

    Look here for the syntax.

    http://office.microsoft.com/en-us/ex...010047494.aspx

    Alan
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Count unique values in a data range with exemption

    Hi lheyanne

    Try the COUNTIFS, assuming your data is in A1:B9

    =COUNTIFS(A1:A9,"Personal",B1:B9,"Cancelled")
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  5. #5
    Registered User
    Join Date
    02-19-2013
    Location
    Philippines
    MS-Off Ver
    Excel for Mac 2011
    Posts
    16

    Re: Count unique values in a data range with exemption

    Quote Originally Posted by Fotis1991 View Post
    Try something like this.

    =SUMPRODUCT((A1:A1000="Personal")*(B1:B1000="Cancelled"))
    After changing "*" to "-" It worked! Exactly what I was looking for. Thank you so much

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count unique values in a data range with exemption

    .....................

  7. #7
    Registered User
    Join Date
    02-19-2013
    Location
    Philippines
    MS-Off Ver
    Excel for Mac 2011
    Posts
    16

    Re: Count unique values in a data range with exemption

    Oops! I think I spoke too soon. It did work for counting one unique value that is "Personal" but if I applied that too to another value say "Public" it'll subtract all the number of cells with the value "Cancelled".

    What I want is to count all "Personal" subtract the number of time the value "cancelled" is found on the SAME ROW but diff column. And count all "Public" but subtract or disregard in count if the value "cancelled" is found on the SAME ROW but diff column.

  8. #8
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count unique values in a data range with exemption

    Now i need a sample workbook..

  9. #9
    Registered User
    Join Date
    02-19-2013
    Location
    Philippines
    MS-Off Ver
    Excel for Mac 2011
    Posts
    16

    Re: Count unique values in a data range with exemption

    I figured it out! :D I combined the sumproduct and countifs formula given in this thread... here's what I came up with:

    =SUMPRODUCT((COUNTIF(MARCH!I6:I305,"personal"))-(COUNTIFS(MARCH!I6:I305,"personal",MARCH!BF6:BF305,"Cancelled")))

    Thank you all so much for your help :D

  10. #10
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Count unique values in a data range with exemption

    hmmmm i seem to be in a different direction here, but i've tried to read the opening post time & time again, so maybe:
    =SUMPRODUCT((A1:A10="Personal")*(B1:B10<>"Cancelled"))

  11. #11
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Count unique values in a data range with exemption

    @ lheyanne

    So why did you do this!
    After changing "*" to "-" It worked! Exactly what I was looking for. Thank you so much

  12. #12
    Registered User
    Join Date
    02-19-2013
    Location
    Philippines
    MS-Off Ver
    Excel for Mac 2011
    Posts
    16

    Re: Count unique values in a data range with exemption

    Quote Originally Posted by Kevin UK View Post
    @ lheyanne

    So why did you do this!
    What do you mean?

    Why I changed the "*" to "-"? Because when I tried the the first formula given (SUMPRODUCT), what it did is count all the "personal" with "cancelled". What I want is to count all "personal" without "cancelled". So i figured to subtract it instead. That worked for when I only have the value "personal" in my range column but when there's another value say "public", it subtracts all "cancelled" whether the value in the range column is "public" or "private".

    So I tried the next formula given (COUNTIFS) and got a different result. What it did is count only the value with "cancelled", I was looking for the opposite so I tried combing the two formulas given and got the result that I wanted.

    So yeah, thank you @ Kevin UK for the COUNTIF formula and thanks to @ Fotis1991 for the SUMPRODUCT formula Very much appreciated.

  13. #13
    Registered User
    Join Date
    02-19-2013
    Location
    Philippines
    MS-Off Ver
    Excel for Mac 2011
    Posts
    16

    Re: Count unique values in a data range with exemption

    Quote Originally Posted by benishiryo View Post
    hmmmm i seem to be in a different direction here, but i've tried to read the opening post time & time again, so maybe:
    =SUMPRODUCT((A1:A10="Personal")*(B1:B10<>"Cancelled"))
    Exactly what I was looking for! :D Thank you so much, this formula is way simpler than what I did.

  14. #14
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count unique values in a data range with exemption

    So this is a real long formula...Maybe it's better to try Ben's formula? Post#10??

  15. #15
    Registered User
    Join Date
    02-19-2013
    Location
    Philippines
    MS-Off Ver
    Excel for Mac 2011
    Posts
    16

    Re: Count unique values in a data range with exemption

    Yes, he brought it to my attention. Can't believe I missed his formula, it's way simpler and technically correct formula to use.

    Again, thank you all so much for the help and taking the time to answer.

  16. #16
    Registered User
    Join Date
    02-19-2013
    Location
    Philippines
    MS-Off Ver
    Excel for Mac 2011
    Posts
    16

    Re: Count unique values in a data range with exemption

    I have a follow up question (not sure if this requires a new post):

    Now that I have successfully counted all the "personal" values less the "cancelled" ones, I want to be able to add the total amount corresponding to those "personal" values located on the same row diff column. Again, I want the formula to ignore those rows that are "cancelled."

    Example:
    A1 = Personal, B1 = 1650
    A2 = Personal, B2 = 3200 C2 = Cancelled
    A3 = Personal, B3 = 2850

    I want to achieve the value = 4500 (1650+2850) Ignoring the value in B2 since it's cancelled.

    Thank you.

  17. #17
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count unique values in a data range with exemption

    =sumproduct((a1:a100="personal")*(c1:c100<>"cancelled)*(b1:b100))

  18. #18
    Registered User
    Join Date
    02-19-2013
    Location
    Philippines
    MS-Off Ver
    Excel for Mac 2011
    Posts
    16

    Re: Count unique values in a data range with exemption

    Quote Originally Posted by Fotis1991 View Post
    =sumproduct((a1:a100="personal")*(c1:c100<>"cancelled)*(b1:b100))
    Got it, it worked! Again, thank you! :D

  19. #19
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,709

    Re: Count unique values in a data range with exemption

    Iheyanne;
    For future reference, here is a link to a video I happened to see this morning that I think is beneficial to anyone that has not used SumProduct regularly.

    http://www.youtube.com/watch?v=i4lEP...em-subs_digest

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.08624 seconds
  • Memory Usage 9,362KB
  • Queries Executed 14 (?)
More Information
Template Usage (32):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (5)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (19)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (19)postbit_legacy
  • (19)postbit_onlinestatus
  • (19)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (47):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1