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
Download SmartPCFixer to Fix It (Free)
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
If this answer solves your problem, please check Mark as Answered.
If this answer helps, please click the Vote as Helpful button.
Cheers, Shane Devenshire
Don't Download Malware
Where are you getting the download?
There are malicious people who download valid copies of a popular download, modify the file with malicious software, and then upload the file with the same name. Make sure you are downloading from the developer's web page or a reputable company.
Don't install download manager
Many sites suggest or require you to install an installer or a download manager before allowing you to download a program you may be interested in downloading. These tools almost always cause your computer more problems and may even have malware or other spyware. Avoid any site claiming anything must be installed first before you can continue with your download.
Avoid advertisements on download pages
To help make money and pay for the bandwidth costs of supplying free the software, the final download page may have ads. Watch out for anything that looks like advertisements on the download page. Many advertisers try to trick viewers into clicking an ad with phrases like "Download Now", "Start Download", or "Continue" and that ad may open a separate download.
Cancel or deny any automatic download
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.
Related: How Can You Update & Download NVidia 6100, 6800 GS/XT Display Driver v.260.89 WHQL,Where to Download NVidia GeForce 8800 GT WHQL-certified driver v.196.21,How to Update & Download NVidia GeForce Go 7400 Display Driver v.260.19.12,[Solved] Download NVidia GeForce GTX 460M Driver v.331.82,Download NVidia Quadro Plex Model IV VGA Driver v.304.43 Certified,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.8.17.12.6686,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.11.6.0.1030,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.9.3.0.1019,Method to Update & Download ASUS K41VD Intel INF Update Driver v.9.1.1.1015,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver
Read More: Troubleshoot:converting all earlier works files to ms office 2003 files,[Anwsered] Ctrl+t keyboard shortcut does not work to CUT in Paint.,Corrupted Recycle bins in Windows 7 Home Premium [Anwsered],How to Resolve - Copy and paste from web page..?,[Anwsered] Copying a pivot table,Closing Internet Explorer causes error,Compare 2 Tables and Calculate the PROFIT Faster,Computer goes to Windows Boot Manager after Start,Connect to one of my network computer remotely when don't allow exception box Marked,computer locking up randomly
No comments:
Post a Comment