# Count unique values in a data range with exemption

1. ## 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. ## Re: Count unique values in a data range with exemption

Try something like this.

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

3. ## 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

4. ## 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")

5. ## Re: Count unique values in a data range with exemption

Originally Posted by Fotis1991
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. ## Re: Count unique values in a data range with exemption

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

7. ## 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. ## Re: Count unique values in a data range with exemption

Now i need a sample workbook..

9. ## 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. ## 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. ## 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. ## Re: Count unique values in a data range with exemption

Originally Posted by Kevin UK
@ 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. ## Re: Count unique values in a data range with exemption

Originally Posted by benishiryo
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. ## 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. ## 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. ## 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. ## Re: Count unique values in a data range with exemption

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

18. ## Re: Count unique values in a data range with exemption

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

19. ## 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.

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

#### 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 (?)
Template Usage (32):
• (5)bbcode_quote
• (1)footer
• (1)forumrules
• (1)gobutton
• (19)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (19)postbit_legacy
• (19)postbit_onlinestatus
• (19)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
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
• ./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_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_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• 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