Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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)

share|improve this question
    
@SivaCharan It may help to look at the preview when you edit. There was an extra tab character that was throwing things off. –  soandos Feb 16 '12 at 20:28
    
Do you want the number of males, or something else about them? –  soandos Feb 16 '12 at 20:29
    
thanks @soandos for the update. I will take care next time, while updating... –  Siva Charan Feb 16 '12 at 20:30
1  
@Tom: Are you looking for eliminating duplicates and displaying unique results? –  Siva Charan Feb 16 '12 at 20:32
    
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 Feb 16 '12 at 20:35

2 Answers 2

up vote 0 down vote accepted

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

share|improve this answer
    
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 –  Tom Feb 17 '12 at 16:59
    
I edited my answer to include another condition... –  barry houdini Feb 17 '12 at 18:23
    
thank you so much this was just what I needed! –  Tom Feb 17 '12 at 18:44

I think, you are looking for this output:-

  1. Click Data
  2. Click Advanced from Sort & Filter
  3. Do as per my screenshot
  4. Result will be at F and G Columns

enter image description here

Update:

Count the unique cells,

=SUM(IF(FREQUENCY(B:B,B:B)>0,1))

Result:

6

Example Data:

A           B
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
share|improve this answer
    
ok its a little more complicated I guess –  Tom Feb 16 '12 at 20:46
    
@Tom: so you need same output with formulas.. or something else –  Siva Charan Feb 16 '12 at 20:48
    
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 Feb 16 '12 at 20:56
    
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 Feb 16 '12 at 21:02
    
@Tom: I have updated the answer. –  Siva Charan Feb 16 '12 at 21:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.