Microsoft Office Forums How to count multiple values in a single cell, except zero?
 Register FAQ Search Today's Posts Mark Forums Read

#1
04-13-2011, 09:35 PM
 iuliandonici Windows XP Office 2007 Novice Join Date: Apr 2011 Posts: 4
How to count multiple values in a single cell, except zero?

I have a cell that has multiple values, separated by ";". For example: "35; 255; 67; 0". In the next cell, I would like to know how can I count these values, except zero. Count not SUM them.

This single cell, can have up to 50 values and each value can have up to 7 digits.

I have looked up for COUNT, COUNTA, COUNTIF functions but didn't find any relevancy to what I'm trying to do. Can you, please help me?
#2
04-13-2011, 09:45 PM
 macropod Windows 7 32bit Office 2000 Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 21,938

Hi iuliandonici,

Code:
```Sub InOut()
Application.ScreenUpdating = False
Dim StrIn As String, StrOut As String, i As Long, k As Long
Dim StrSub As String, StrAdd As String, j As Long, l As Long
With ActiveSheet()
For i = 7 To .Range("C" & .Rows.Count).End(xlUp).Row
StrSub = ""
k = 0
l = 0
StrIn = Trim(Replace(.Cells(i, 3).Value, ";", ""))
StrOut = Trim(Replace(.Cells(i, 4).Value, ";", ""))
If UBound(Split(StrIn, " ")) = UBound(Split(StrOut, " ")) Then
For j = 0 To UBound(Split(StrIn, " "))
StrAdd = StrAdd & CLng(Split(StrIn, " ")(j)) + CLng(Split(StrOut, " ")(j)) & " "
StrSub = StrSub & CLng(Split(StrIn, " ")(j)) - CLng(Split(StrOut, " ")(j)) & " "
If Split(StrIn, " ")(j) <> 0 Then k = k + 1
If Split(StrOut, " ")(j) <> 0 Then l = l + 1
Next
StrSub = Replace(StrSub, " ", "; ")
Else
StrAdd = "IN/OUT item counts differ"
StrSub = "IN/OUT item counts differ"
End If
.Cells(i, 6).Value = StrSub
.Cells(i, 7).Value = k
.Cells(i, 8).Value = l
Next
End With
Application.ScreenUpdating = True
End Sub```
The additional data go in columns G (In) & H (Out).
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

 Tags count multiple, multiple values, single cell

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post iuliandonici Excel 4 04-13-2011 09:27 PM bundy5150 Excel 4 02-22-2011 10:00 AM Learner7 Excel 1 07-19-2010 10:06 AM mccruise Word 0 04-07-2010 04:13 AM RobotChicken Excel 3 01-06-2010 09:17 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:51 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top