Help with cells contents sum

[Nanou]

Here is a sample of my column cells contents:

2DO, 5DI (this data is in one cell)
1DO, 2DI
AI
AO

I need to get a total of "DO", total of "DI" ...etc. separately
I have tried countif, sumproduct...but not getting the right result!

Would appreciate any help.
Yasmina

 

[Miss Kitty]

Try this.
=SUMPRODUCT(--(sheet!$D$2:$D$1191="DO"), --(sheet!$V$2:$V$1191="DI Leader"))

Change the word SHEET to the tab/sheet name you are referencing from.
Hope this helps.

 

[Nanou]

Miss Kitty,

It did not work. returning value of "0". Thanks though!

 

[Roger Govier]

Hi

Try
=SUMPRODUCT(--(ISNUMBER(FIND("DO",A1:A100))))
Change range to suit.

 

[Roger Govier]

Hi

Try
=SUMPRODUCT(--(ISNUMBER(FIND("DO",A1:A100))))
Change range to suit

It might be better to put the "DO" or "DI" in a cell e.g. in C1 , then you
can just use the formula
=SUMPRODUCT(--(ISNUMBER(FIND(C1,A1:A100))))

 

[Nanou]

Hi Roger,

Thanks for your help. But as I said I already used the formula and what
happened is that it is bringing me a wronge count. I explain:
Based on the example I posted in my original message it will give me " 2 "
as result for my " DO " count , where it should be " 3" (number of actual
"DO" )

Thanks for any help!



the following result for "do" my example

 

[Gord Dibben]

=SUM(LEFT(A1),LEFT(A2))*1

Returns 3


Gord Dibben MS Excel MVP

 

[Nanou]

Gord,

what is that * 1 for ?

=SUM(LEFT(A1),LEFT(A2))*1

Returns 3


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Just in case the formula returns text.

The *1 changes it back to a usable numeric.

Not necessary in this case.............just me out of habit<g>


Gord

 

[Nanou]

Oh..then it did not work for my real column, any other solution!? This is
driving me crazy!

 

[Gord Dibben]

I missed the part about 1DO, 5DI in one cell.

If the data is consistent as your example............

In B1 enter =SUM(LEFT(A1),LEFT(A2))

In C1 enter =SUM(MID(A1,6,1),MID(A2,6,1))


Gord

 

[Nanou]

Hummm...u lost me here Gord!

I missed the part about 1DO, 5DI in one cell.

If the data is consistent as your example............

In B1 enter =SUM(LEFT(A1),LEFT(A2))

In C1 enter =SUM(MID(A1,6,1),MID(A2,6,1))


Gord

 

[Gord Dibben]

Did you try the formulas?

Your example data in A1 and A2

2DO, 5D
1DO, 2DI

Try the formulas in B1 and C1

I get 3 for DO in B1 and 7 for DI in C1

Isn't that what you wanted?

where it should be " 3" (number of actual
"DO" )

Gord

 

[Nanou]

Yes Gord,

That would be easier if the column was as short as the sample i emntionned.
In my worksheet, I got 1588 rows..all carry the kind of data I specified in
the sample , the cells that are giving me hard time are the ones that have
XDO, & YDI.
I did try the formulas but I get "# value" as result!!!

The only solution I am left with is to split each column into multiple ones
so I can count the X & Y of those DO and DI.

Any other suggestions are welcome.

Thanks a lot Gord!

 

[Gord Dibben]

X and Y are not numbers so you can't "sum" them as you can 2DO and 3DO so
you will get the error.

You would have to go back to one of the pther poster's formulas for finding
DO and DI

My formulas dealt only with your examples.

See Roger Govier's posting with SUMPRODUCT


Gord Dibben MS Excel MVP

 

[Nanou]

Great Gord, I appreciate your help. WIll take a look at Roger post!
Have a great day!