## Counting the number of occurrences of a text in a column

I'm back.
I have tried Ron's formula:  With
Col_A containing dates
and
Col_B containing names

AND...
D1: a date to match
E1: a name to match

This formula returns the count of that combination
F1: =SUMPRODUCT((A:A=D1)*(B:B=E1))
Shane's formula:
I don't see why Ron's formula doesn't work, but you might try
=SUMPRODUCT(--(A1:A50=D3),--(B1:B50=E3))
Where you enter the date and name in D3 and E3 as originally suggested.  There are some cases in which -- is safer than simple multiplication.  And I shortened the range because maybe you have stuff below the area of concern which is causing a problem.
and I still can't seem to get a correct count.

Here is what I am seeing:
Date              Office     Ron Formula    Shane Formula    Actual Count
5/3/2010      Valley          17                       17                       73
5/4/2010      Valley          15                       15                       60
5/5/2010      Valley          12                       12                       70
5/6/2010      Valley          12                       12                       52
5/7/2010      Valley            4                         0                        52
Date Data Column is D:D
Office Data Column is C:C
Date Formula Column is I2
Office Formula Column is J2
Ron Formula used is =SUMPRODUCT((D:D=I2)*(C:C=J2))
Shane Formula used is =SUMPRODUCT(--(D:D=I2),--(C:C=J3))
I have done a cut & paste to insure Valley and date are identical but I can't seem to get either formula to count the actual number.
What am I doing wrong?  I really don't understand the difference in the 5/7/2010 results between Ron & Shane.
Thanks for
all y'alls help.

## Anwsers to the Problem Counting the number of occurrences of a text in a column

Hi,
The most likely problems are 1.
Dates not entered as dates but as text.
2.
Spelling problems in the Office field especially unseen spaces.
But the fact is that as defined there is nothing wrong with the formulas.
By the way, even though you don't want  a pivot table, you can use the pivot table as a double-check against the formula approach - it should return the same results.  Place the Office in Row Labels field, put the Date collumn in the Row Labels field, put
the Office in the Values field.  Suppress the display of subtotals in the row labels area, change to Report Layout in Tabular Form.  End result something like this:

Office
Date
Count of Office

Valley
5/3/2010
3

5/4/2010
2

5/5/2010
2

5/6/2010
2

5/7/2010
2

Cheers, Shane Devenshire

Some sites may automatically try start a download or give the appearance that something needs to be installed or updated before the site or video can be seen. Never accept or install anything from any site unless you know what is downloading.

## Recommended Method to Fix the Problem: Counting the number of occurrences of a text in a column:

### How to Fix Counting the number of occurrences of a text in a column with SmartPCFixer?

1. Download Error Fixer . Install it on your system.  Click Scan, and it will perform a scan for your computer. The junk files will be shown in the list.

2. After the scan is done, you can see the errors and problems need to be repaired. Click Fix All.

3. The Fixing part is done, the speed of your computer will be much higher than before and the errors have been fixed.