Excel - Count if two cells are equal

[coolkake]

Hi

I've looked for a way to do this but I can't seem to find what I'm looking for so here's my issue

I want to count all the location where colum B equals what is in colum C in the spread sheet. Below is an example and there are blank fields in between each of these entries; sometimes just one and other times there are 20. Grouping is used on this spreadsheet.

Any help would be greatly appreciated.

Thanks

Ken

B C
13 13
1 13
1 15
1 13
1 10
1 12
1 22
1 16
1 15
1 12
20 20

 

[SkipVought]

Hi,

All kind of ways to do this...

in col D
[tt]
=if(B1=C1,1,0)
[/tt]
copy down

then SUM column D

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[coolkake]

Is there a way to have this ignore null values? This works great but when I try do paste is for the entire colum all the empty spaces show up with 1 because they match.

 

[SkipVought]

Turn on AutoFilter and select cells that are Not Blank in column D

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SkipVought]

Turn on AutoFilter and select cells that are Not Blank in column B.

THEN use
[tt]
=SUBTOTAL(9,D)
[/tt]


Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[coolkake]

Is there nothing I can put in the fomula to ignore blank cells?
=if(B1=C1,1,0) gives me what I'm looking for but then when I paste this down the colum it adds a 1 to all the blank fields.

SkipVought, don't really want to use the AutoFilter for this particular sheet. Thanks though.

 

[SkipVought]

No Filter needed...
[tt]
=SUMPRODUCT(--(BBB<>"")*(DDD=1))
[/tt]
where BBB is the data range in column B and DDD is the data range in column D

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[beavmetal]

How about this?

=IF(B1-C1=0,1,0)

Assumptions:
1.) You are only working with numbers

 

[SkipVought]

some cells are empty in columns B & C.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[yogia]

Hi Ken:

How about ...

=SUMPRODUCT((B2:B12=C2:C12)*(B2:B12<>""))

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com