-1

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)

Improve this question
7
  • @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, 2012 at 20:28
  • Do you want the number of males, or something else about them?
    – soandos
    Feb 16, 2012 at 20:29
  • thanks @soandos for the update. I will take care next time, while updating...
    – Siva Charan
    Feb 16, 2012 at 20:30
  • 1
    @Tom: Are you looking for eliminating duplicates and displaying unique results?
    – Siva Charan
    Feb 16, 2012 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, 2012 at 20:35

2 Answers 2

Reset to default
1

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

Improve this answer
2
  • 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, 2012 at 16:59
  • I edited my answer to include another condition...
    – barry houdini
    Feb 17, 2012 at 18:23
0

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
Improve this answer
5
  • ok its a little more complicated I guess
    – Tom
    Feb 16, 2012 at 20:46
  • @Tom: so you need same output with formulas.. or something else
    – Siva Charan
    Feb 16, 2012 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, 2012 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, 2012 at 21:02
  • @Tom: I have updated the answer.
    – Siva Charan
    Feb 16, 2012 at 21:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .