Count if two criteria match while ignoring duplicates

Question

I have a simple Sheet with 2 cells that I need to countif 2 conditions match and treat duplicates as 1.

Column A    Column B
Male        closed
Female      invalid #
Male        exception
Male        closed
Male        closed
Female      new

I would like a formula that counts Male closed and return 1 result (ignoring duplicates)

Solution

If you just want to count the different order numbers (in column B) that are complete (column A) try this

=SUM(IF(FREQUENCY(IF(A$2:A$100="Complete",MATCH(B$2:B$100,B$2:B$100,0)),ROW(B$2:B$100)-ROW(B$2)+1),1))

confirmed with CTRL+SHIFT+ENTER

To add another condition, e.g. assuming status in A and Store Id in B as before but with Sales Reps in column C you can use this version

=SUM(IF(FREQUENCY(IF(A$2:A$100="Complete",IF(C$2:C$100="Rep1",MATCH(B$2:B$100,B$2:B$100,0))),ROW(B$2:B$100)-ROW(B$2)+1),1))

of course you can use cells to store the criteria if you wish

Comments

[soandos]

@SivaCharan It may help to look at the preview when you edit. There was an extra tab character that was throwing things off.

[soandos]

Do you want the number of males, or something else about them?

[Siva Charan]

thanks @soandos for the update. I will take care next time, while updating...

[Tom]

2 conditions need to be meet like male + closed (2 columns) however only whant to count that conditions 1 time rather then 1000 times that appears

[Tom]

basic would be if male 1 male 1 male 2 male 3 male 1 in columns looking to count "male 1 search should only return 1 count"

[Siva Charan]

@Tom: here am bit confused. Can you tell whats your expected output?

[Tom]

ok its a little more complicated I guess

[Siva Charan]

@Tom: so you need same output with formulas.. or something else

[Tom]

this set of results should return 6 count because some of the results repeat thank you 941000106879 complete 941000106879 complete 941000106879 complete 941000107445 complete 941000107445 complete 941000107445 complete 941000107445 complete 941000107446 complete 941000107447 complete 941000107451 complete 941000107451 complete 941000107451 complete 941000107451 complete 941000107451 complete 941000107456 complete 941000107456 complete 941000107456 complete 941000107456 complete

[Tom]

thank you! the formula I have now counts them all giving a result of 18 - because some have duplicate ID's I would like a formula that returns a result of 6 thus eliminating multiple order locations 18 orders 6 locations

[Tom]

thank you this works great! can I add a 3rd value to key off like sales code+sales rep+status (counting code only once like before ignoring dupes) so keying off 3 column fields Store ID, Sales rep, Status, ignoring dupes in store ID

[barry houdini]

I edited my answer to include another condition...