Counting specific values in a CSV Column

[David]

I have a column containing several values, for example:

B1, H, G

B2

G4

D, D1

N,B2, L, L3, G1

O1

C

G3

O2, Q2

L6

I1, I2, L4, Q2


How do i find the count for each unique value?

I have used:


=SUM(--NOT(ISERROR(FIND(A3,'SCR Dispo'!N:N)))) as an array (ctrl+Shift+Enter), but it counts the G1, G2 and G3 as G values.


Your insight and suggestions are greatly appreciated.

David

 

[shrivallabha]

I think VBA is better choice if you do not how many unique entries will be there in CSV.


Here's a formula which should probably work the way you want:

=SUM(--ISNUMBER(FIND(A3&",",'SCR Dispo'!N:N&",")))


Note: If possible avoid whole column reference N:N. Use N1:N10000 [Maximum expected] as it will cause a slow down.

 

[David]

Excellent Shrivallabha!!!

Thanx very much. it appears to be working!!


Best Regards,

David

 

[David]

How would I do this if i wanted a sumif, based on the value in another column?

 

[shrivallabha]

I had posted SUMPRODUCT in the old forum.

You can implement a SUMPRODUCT [untested]
=SUMPRODUCT(ISNUMBER(FIND(A3&",",'SCR Dispo'!N1:N100&","))*'SCR Dispo'!B1:B100)
where 'SCR Dispo'!B:B is your SUM column.

 

[David]

Thanx very much... i used a version of sumproduct and it is working...
one thing... sumproduct appears to be a beast with resources... tends to be slow...
any suggestions on improving speed?

 

[shrivallabha]

What version of Excel do you use? Are you referring to 2nd question of summing or something else? Maybe if you could post a sample if it is different.

I am asking this because SUMPRODUCT in 2007+ accepts full column references such as N:N and you could be virtually dealing with million cells. Not a good idea. Restrict them to maximum [N1:N10000] and see if that improves speed.

A SUM formula [array entered] could be used but it'd nearly be equal I suspect.

 

[SirJB7]

Hi, David!
I agree with your beast resource consume of SUMPRODUCT and towards the same direction of shrivallabha's suggestion, I'd use a dynamic named range for 2 reasons: easy expandability and exact and minimum resource's use.
Hope it helps.
Regards!

 

[David]

Here is the cell formula:
=SUMPRODUCT(ISNUMBER(FIND($A3&",",'Dispo'!$O$1:$O$5000&","))*(B$2='Dispo'!$I$1:$I$5000)*(B$1='Dispo'!$G$1:$G$5000))

Thanx very much for your help.
David