CountiF or CountiFs two columns conditions

[Oceans]

Have Column B which has those numbers that are repeated
B7 993925968
B8 993925968
B9 993925968
B10 993925968
B11 992308642

I have column C which has a specific text message =
Blast Email

Now i need to get the Distinct Count of Column C
Since the numbers are repeated in column B, then the count for column C message should be 2.

But my formula =COUNTIF($C$9:$C$90,"Blast Email") does not work for me.



Attached an excel sheet with the example

[momentman]

Maybe you can attempt something like this:

=SUMPRODUCT((C9:C90="Blast Email")*(1/COUNTIF(B9:B90,B9:B90)))

[Oceans]

I found a discrepancy / glitch. the excel sheet i have sent was a sample with a small size data. The actual excel sheet has 1624 rows, so now the formula yields wrong count.

Originally Posted by momentman

Maybe you can attempt something like this:

=SUMPRODUCT((C9:C90="Blast Email")*(1/COUNTIF(B9:B90,B9:B90)))

[Oceans]

Have a look at the second sheet, numbers are off.

Bulk Update should not be 141.666

Originally Posted by momentman

Maybe you can attempt something like this:

=SUMPRODUCT((C9:C90="Blast Email")*(1/COUNTIF(B9:B90,B9:B90)))

[gebobs]

You're just trying to count the number of rows that have "Blast Email" in C? The equation in your first sheet was fine, it just did not include two rows, 7 and 8.

Oh wait...I guess I get it. You want the number of rows with Blast Email and distinct column B numbers. Let me ponder that.

[momentman]

I had to use a helper column

See uploaded sheet

https://www.dropbox.com/s/idwkcxs8pp...oaded_2003.xls

[Oceans]

I can't access DropBox from work.

Can you attach to your reply please.

Originally Posted by momentman

I had to use a helper column

See uploaded sheet

https://www.dropbox.com/s/idwkcxs8pp...oaded_2003.xls

[momentman]

See attached file

[Oceans]

Originally Posted by momentman

See attached file

This works perfect for me, Thanks a bunch.

[Oceans]

Originally Posted by momentman

See attached file

One More thing please, I have added the formula to Sheet3 to compare initial result to the actual count on Sheet1
and wanted to use the Find & select, Go to Special, Row Differences, so it can highlight the different rows, but since it is comparing a number in a cell to a formula in the other cell, all are highlighted as different. is there another trick to highlight the different cells ?, i have attached the file again.

[momentman]

I used conditional formatting. See attached worksheet

[Oceans]

Originally Posted by momentman

I used conditional formatting. See attached worksheet

Yes, that is how it should be
Thanks.