Re: Help with cells contents sum

Re: Help with cells contents sum
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

On Wed, 27 Aug 2008 13:19:00 -0700, Nanou 
wrote:

>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" wrote:
>
>> 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
>> 
>> 
>> On Wed, 27 Aug 2008 04:57:01 -0700, Nanou 
>> wrote:
>> 
>> >Hummm...u lost me here Gord!
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> 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
>> >> 
>> >> On Tue, 26 Aug 2008 13:01:01 -0700, Nanou 
>> >> wrote:
>> >> 
>> >> >Oh..then it did not work for my real column, any other solution!? This is 
>> >> >driving me crazy!
>> >> >
>> >> >"Gord Dibben" wrote:
>> >> >
>> >> >> 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
>> >> >> 
>> >> >> 
>> >> >> On Tue, 26 Aug 2008 11:54:06 -0700, Nanou 
>> >> >> wrote:
>> >> >> 
>> >> >> >Gord,
>> >> >> >
>> >> >> >what is that * 1 for ?
>> >> >> >
>> >> >> >"Gord Dibben" wrote:
>> >> >> >
>> >> >> >> =SUM(LEFT(A1),LEFT(A2))*1
>> >> >> >> 
>> >> >> >> Returns 3
>> >> >> >> 
>> >> >> >> 
>> >> >> >> Gord Dibben  MS Excel MVP
>> >> >> >> 
>> >> >> >> On Tue, 26 Aug 2008 09:35:02 -0700, Nanou 
>> >> >> >> wrote:
>> >> >> >> 
>> >> >> >> >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
>> >> >> >> >"Roger Govier" wrote:
>> >> >> >> >
>> >> >> >> >> 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))))
>> >> >> >> >> 
>> >> >> >> >> 
>> >> >> >> >> -- 
>> >> >> >> >> Regards
>> >> >> >> >> Roger Govier
>> >> >> >> >> 
>> >> >> >> >> "Nanou"  wrote in message 
>> >> >> >> >> news:D0EF6FDB-E954-4FEA-9633-3C9C71CAF733@microsoft.com...
>> >> >> >> >> > 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 
>> >> >> >> >> 
>> >> >> >> >> 
>> >> >> >> 
>> >> >> >> 
>> >> >> 
>> >> >> 
>> >> 
>> >> 
>> 
>>
date: Wed, 27 Aug 2008 15:09:03 -0700 author: Gord Dibben gorddibbATshawDOTca