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

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

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.

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

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.

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.

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.

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.

Originally Posted by Fotis1991

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

Got it, it worked! Again, thank you! :D