Excel counting 1

[liltechy]

I am trying to count the occurrences of text in two different columns in Excel. The answer to the count should be on worksheet "RejectSummary" and the text occurrences is on worksheet "SWI". Below is the formula I entered but I keep getting 0. It should be 191.

=SUM(SWI!F2:F65536="A13&quot*(SWI!G2:G65536="ESID De-energized&quot

Pleas help

liltechy

 

[KenWright]

I'm assuming you have a value in A13 that you want this to equal, so i removed the quotes, but if not and you actually have A13 in each of the cells in question, then just put the quotes back.

Try the following - Must be array entered - CTRL+SHIFT+ENTER

=SUM((SWI!F2:F65536=A13)*(SWI!G2:G65536="ESID De-energized&quot)

or without having to array enter

=SUMPRODUCT((SWI!F2:F65536=A13)*(SWI!G2:G65536="ESID De-energized&quot)

Regards
Ken.................

 

[liltechy]

I tried both formulas and I am still getting 0 as my count.
Why is it not working?

liltechy

 

[GlennUK]

I would test to make sure that the entries you are testing really have
"ESID De-energized" and "A13" ( or contents of A13 ),
as their contents, and don't have trailing spaces or some non-display characters attached.

Put =IF(SWI!Grownum="ESID De-energized","OK","wrong&quot in a cell to see if "OK" comes out. ( Replace rownum with a row number that you know contains "ESID De-energized" )

In other words do a little debugging to see what parts of the formula are failing.

Glenn.

 

[liltechy]

I did the debugging and when I enter the actual info into the formula I still get 0. Here is what the actual info looks like in the formula:

=SUM((SWI!F2:F65536="NFI &quot*(SWI!G2:G65536="MVI_AND_OR_MVO_EXISTS&quot)

Please help

liltechy

 

[xlbo]

Sure you got the right number of spaces in there ??

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[is1ma1il]

have you tried countif?

 

[Wray69]

Did you follow Ken's instructions and make it an array? By Hitting CTRL+SHIFT+ENTER at the end of the formula? Because that should work for you.

Regards,

Wray

 

[liltechy]

It worked when I did the CTRL+SHIFT+ENTER, Thanks. But now how do I get it to work is the cell is empyy?

liltechy

 

[KenWright]

Sorry, you need to be a bit more specific - What cell is empty? What are you trying to count?

Regards
Ken...............

 

[liltechy]

The second criteria is empty.

=SUM((SWI!F2:F65536="NFI &quot*(SWI!G2:G65536="MVI_AND_OR_MVO_EXISTS&quot)



liltechy

 

[KenWright]

=SUM((SWI!F2:F65536="NFI &quot*(SWI!G2:G65536="&quot)

Regards
Ken................

 

[liltechy]

When I enter this formula I get 0 as the answer and it should be 62

=SUM((MVI!$F$2:$F$65536="A76 &quot*(MVI!$G$2:$G$65536="&quot)

I am doing CTRL+SHIFT+ENTER to make it an array. What am I doing wrong?

Linda

 

[KenWright]

I don't understand what the ="A76 &quot bit is trying to do. If A76 is a cell reference, then it needs to be just:-

=A76)

This assumes your criteria is to be whatever is in cell A76.

Regards
Ken.....................

 

[liltechy]

"A76 " is the data that is in the cell. This data comes from an external source.


liltechy

 

[KenWright]

As long as you have the number of spaces right, then the formula works fine. If it isn't then you are not seeing what you think you are seeing. If you are able then by all means send me the sheet and point out what is not working. ken.wright at ntlworld.com

Regards
Ken.................

 

[KenWright]

Still working through it, but at first glance the data you are searching isn't what you thought you were seeing. Take your A76's. The value in the cell is "A76" and not "A76 &quot. The ecll length is 3 characters long, so you will never get any results looking for what you are looking for. The ZIP cells are the same. The cells with A13 have 27 spaces in them, and other cells are in a similar state. There is no consistency between them, so you will never be sure that the data is good.

I would suggest you use something like Dave McRitchies TrimAll macro to clean your data as soon as you have imported it. It takes seconds and will save you so much heartache. If you want you can even tie it to a macro and a button, so that all you have to do is hit the button. This will get rid of all spaces etc in the data selected

Macro is as follows:-

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
Cell.Value = Application.Trim(Cell.Value)
Next Cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Other point to note is that once you have cleaned your data this way, you can then actually use the data in your table as the criteria instead of having to build it into the formula. I'll fix your spreadsheet and send it back as per the above.

Regards
Ken...................

 

[xlbo]

Good work Ken

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[KenWright]

OP sorted privately by email. Problems all centered around last message where there were an inconsistent number of trailing spaces. Formulas all changed for ones using SUMPRODUCT, for no other reason than that I prefer it.

Introduced a macro that selected the particular columns affected, and then ran Dave McRitchies TrimAll macro. Hooked it up to a button on the summary sheet with advice that the OP run it every time she imports data.

Regards
Ken.................