Two different totals in one cell

C	D	L	O	P	Q	R
H, AL	A, AL	A	A	P	SF	H, AL
1.0, 1.0	1.0, 1.0	7.50	8.5	0.00	0.00	1.0, 2.0

As you can see, “A” (shown in columns D and L) and “AL” (shown in columns C 
and D) are in two separate columns;

I would like to show their totals in two separate columns, i.e, O (totals of 
all “A”) and R (totals of “AL”) in the example above.

0
Utf
5/4/2010 3:27:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
693 Views

Similar Articles

[PageSpeed] 7

Try these:

=3DCOUNTIF(C1:L1,"*A*")

=3DCOUNTIF(C1:L1,"*AL*")

Hope this helps.

Pete

On May 4, 4:27=A0pm, something68 <somethin...@discussions.microsoft.com>
wrote:
> C =A0 =A0 =A0 D =A0 =A0 =A0 L =A0 =A0 =A0 O =A0 =A0 =A0 P =A0 =A0 =A0 Q =
=A0 =A0 =A0 R
> H, AL =A0 A, AL =A0 A =A0 =A0 =A0 A =A0 =A0 =A0 P =A0 =A0 =A0 SF =A0 =A0 =
=A0H, AL
> 1.0, 1.0 =A0 =A0 =A0 =A01.0, 1.0 =A0 =A0 =A0 =A07.50 =A0 =A08.5 =A0 =A0 0=
..00 =A0 =A00.00 =A0 =A01.0, 2.0
>
> As you can see, =93A=94 (shown in columns D and L) and =93AL=94 (shown in=
 columns C
> and D) are in two separate columns;
>
> I would like to show their totals in two separate columns, i.e, O (totals=
 of
> all =93A=94) and R (totals of =93AL=94) in the example above.

0
Pete_UK
5/4/2010 4:01:12 PM
Correction:  This did not work for what I need.  I need to show the totals 
found on the second row, i.e. the totals of the 1.0, 2.0, etc. but keeping 
separate and together.  I have an excel sheet showing what I need if I am too 
confusing here.


"Pete_UK" wrote:

> Try these:
> 
> =COUNTIF(C1:L1,"*A*")
> 
> =COUNTIF(C1:L1,"*AL*")
> 
> Hope this helps.
> 
> Pete
> 
> On May 4, 4:27 pm, something68 <somethin...@discussions.microsoft.com>
> wrote:
> > C       D       L       O       P       Q       R
> > H, AL   A, AL   A       A       P       SF      H, AL
> > 1.0, 1.0        1.0, 1.0        7.50    8.5     0..00    0.00    1.0, 2.0
> >
> > As you can see, “A” (shown in columns D and L) and “AL” (shown in columns C
> > and D) are in two separate columns;
> >
> > I would like to show their totals in two separate columns, i.e, O (totals of
> > all “A”) and R (totals of “AL”) in the example above.
> 
> .
> 
0
Utf
5/5/2010 2:57:01 PM
Reply:

Similar Artilces:

How do I plot several sets with different x values in the same plo
I need to know how can I plot in the same graph several sets (x,y) with different (and independent) values of the x for each. The scale is the same, but the values are different for each group. For example, in one of the sets, I have the y values for x=314,416.5,1290... and in other, I have the y values for x= 50,147,670... And I want to have the two different lines in the same graph, but one sparated from the other. Select the first series of XY values, Insert Chart XY, & select options to suit. Go back to your data sheet, select your second series of XY values, copy, go back to t...

only one group per page
How do you force the report to make a new page ? I need to group items , but only one type per page.. or if its to big tow pages but only one type in a page On Thu, 21 Jun 2007 13:44:03 -0700, zionsaal@gmail.com wrote: > How do you force the report to make a new page ? > I need to group items , but only one type per page.. or if its to big > tow pages but only one type in a page Group the report by Item (in the report Sorting and Grouping dialog). Then set the Item Group Header ForceNewPage property to Before Section. It's on the group header properties format tab. -- Fred Pl...

HELP! How to copy cell with leading zeros &/or fixed length ??
I need to copy the daa from several cells to one. I need to maintain specified character length or numbers with leading zeros in each cell after I combine them into a single cell. I'm trying to use =C1&C2&C3 Example: Cell Length Contents C1 5 00067 C2 3 XY C3 6 abcde so I should end up with 00067 XY abcde after I combine them. Any help appriceated. thanks. try =F7&TEXT(F8,"00000") -- Don Guillett SalesAid Software donaldb@281.com "tmb" <topmailbox@yahoo.com> wrote in message news:o1X%d.202663$qB6.152820@torna...

automatically select cell in another column
Hi, I have a question. Suppose you have three columns. R1 Col1 Col2 Col3 R2 5 100 20 R3 10 200 R4 25 300 R5 30 50 R6 35 0 R7 40 0 I want to calculate the value in Col3 (20 in this case) with a value in Col1 for which the corresponding value in Col2 is just before 0. That is, Multiply 20 with 30 (R6Col2=0, so select R5Col1). Now if I change the values in Col2, the calculated result should reflect the change automatically. I am wondering if it is possible to do this in Excel, peferably without writing a macro! Thanks. -ab Hi try the following (if...

The receipt format is totally different from the preview
My printer is EPSON TM88III and I installed the OPOS driver. The receipt printed has a different font and layout from the one in the receipt preview, e.g., in the preview the "Sales Receipt" is in a white box with black border while in the receipt it is in a black box. How can I solve this problem? Thank you! -- Nothing Is this all because of the OPOS driver? -- Nothing "NothingMuch" wrote: > My printer is EPSON TM88III and I installed the OPOS driver. The receipt > printed has a different font and layout from the one in the receipt preview, > e.g., in...

Same fund in two broker accounts
I own the same mutual fund in two brokerage account. When I go to add the fund to the second account it Money tells me that the symbol is already in use and will not let me assign a symbol to the fund. Because of this its value is not tracked when Money updates prices. How can I add this second entry? Cliff Sorry for the second post "Cliff Lane" <C_LANE_MD@email.msn.com> wrote in message news:uApgVPdSEHA.2128@TK2MSFTNGP09.phx.gbl... > I own the same mutual fund in two brokerage account. When I go to add the > fund to the second account it Money tells me that the s...

apply a formula to multiple cells
hi, a colleague of mine presented me with this problem and i've failed miserably. He has several hundred cells with a value in each. He wants to apply a single formula to all cells. Is there as easy way of adding the formula to each cell? - Without having to cut and past the formula into each cell. -- Cheers Stuart You don't have to do that. Enter it in the first cell then cut and paste in one operation to all the others. -- HTH Bob Phillips "FixitFrog" <FixitFrog@discussions.microsoft.com> wrote in message news:C9ABA6A0-98DA-4356-B894-5E2FEF82E3A0@microsoft...

How to connect two organizations exchange 2003?
Hi, i have two domains (AD win2003), and one exchange at each. I wish to replicate users from one domain into contact at the other exchange. Is this possible? How can I do that? You will need to use the ADC for Inter-Organization Synchronization. This link is very helpful. http://www.microsoft.com/resources/documentation/exchange/2000/all/reskit/en-us/part5/c20inter.mspx "Ricardo Pistarino" <RPistarino@hotmail.com> wrote in message news:uk7ZEfhXEHA.4000@TK2MSFTNGP09.phx.gbl... > Hi, i have two domains (AD win2003), and one exchange at each. > I wish to replicate ...

Two customised A5 pages per A4 page
I share this in case it saves someone else some time. Obviously for some it will be "so what?". I wanted to have a Publisher-designed page appear twice per sheet, with a couple of fields changing each time. While a "mail merge"-type facility exists in Publisher (as at Publisher 2003), my first attempts were disappointing. To make it work for this purpose, I needed to: i) From Tools>Mail and Catalog merge>[...] Wizard, choose the Catalog function. This is how to get multiple customised "blocks" onto a page. ii) Set your page margins to zero. (Arra...

automatically selecting contiguous cells
Using 2007. What am I doing that will cause the automatic selection of 2-3 continuous cells? I will just be working away, then the next time I click on a cell, two or three cells will get selected. Then ever after in that session everytime I click , again, two or three cells will become selected. I am unconsciously doing something - but I don't know what. There are no formulas, no auto filling, nothing complicated going on. Thanks to someone who can clear up this mystery to me. I'd guess you have merged cells there. -- HTH, Barb Reinhardt "becder" wrote: > Usi...

Macro use in different location
I create a macro (excel 2003)-security low or medium- in one location and then when I try to run the macro in another location it just goes back and repeats itself in the original location. How do I get the macro to be independent of the originial creation location? Hi post your code :-) -- Regards Frank Kabel Frankfurt, Germany "dersu" <dersu@discussions.microsoft.com> schrieb im Newsbeitrag news:44B1AE50-F5C5-4249-BBD5-D15FFE0AF7B4@microsoft.com... > I create a macro (excel 2003)-security low or medium- in one location and > then when I try to run the macro in an...

Finding matches in two columns
I have two columns of years. Column A has 27 years from 1911 to 2007 indicating a particular year a house was built. Column B has all years from 1800 to 2009 use to plot a graph. In column C I want to find if any of the years from 1800 through 2009 matchs one of the 27 years in column A and return a 1 if that year matches or a 0 if it does not. With the results in column C I can graph all years on the x axis and which one of them had a house built. Erik (a Word and Excel 2007 user) One way using COUNTIF In C2: =IF(B2="","",IF(COUNTIF(A:A,B2),1,0)) Copy down...

.exe files not working
I'm using Norton 360 on both machines. On my machine, all the dozens of shortcut links on my desktop have lost their color and their ability to start the associated .exe program. I have to associate with the program, but the computer won't remember that the next time. "Cymbal Man Freq." <disasterpastor45@gmail.com> wrote in message news:OhyLtLr4KHA.5808@TK2MSFTNGP02.phx.gbl... > I'm using Norton 360 on both machines. > > On my machine, all the dozens of shortcut links on my desktop have lost > their color and their ability to star...

maximum charachters in cells
I try to write a number with 30 figures (number), like 123,456,789,123,456,789,123,456,789,012. In the cell the result was 123,456,789,123,456,000,000,000,000,000. My problem is that I want to divide my number to 97 but the result is modified by Excel taking in to account the result with many zeros. I need to make this operation for IBAN codes (new banks account number in Europe) but the Excel do not allow me. If anybody can explain me this error, please answer me to e-mail : negoescuconstantin@romtelecom.ro Hi this is not an error but a documented limitation: Excel supports only 15 si...

"Batch swap" words within a cell/column
I have column on a spreadsheet called Primary/Vendor(one cell), and under it is a list of Primary's followed by the main vendor. Like so: A 1 Sapphire/CapOn 2 Monument/Trilegian 3 Sapphire/Wachovi and so on There are about 100 of these, and I need to swap it to say Vendor/Primary so the list would then read CapOne/Sapphir Trilegiant/Monumen Wachovia/Sapphir and so on.. Any way of easily doing this Hi, There sure is an easy way; use Data->text to columns, uncheck tab (or whatever is defaulted) and check other- put a slash in the box, then OK. then ...

Synchronizing two calendars
Hello, I have two calendars : - one on our Exchange server - one in my PST file (Outlook 2002) Is there any simple way to synchronize them ? Thanks ! Any particular reason you're not using an Offline Folder file as opposed to a Personal Folders (PST) file? "myname" <nospam@ihatespam.com> wrote in message news:4386e071$1@news.sncf.fr... > Hello, > > I have two calendars : > - one on our Exchange server > - one in my PST file (Outlook 2002) > > Is there any simple way to synchronize them ? Well, I depend upon our administrators' will :c) ...

Multiple lists in a cell
Is it possible to have a list range in one cell based on the inputed information in that cell? IE, I have a packing list for 3 different products, and 3 different master lists. On the 1st list, I have added in list form in one cell the 3 different lists I could possibly generate. Is it possible to change a list reference in the same cell based on the list I want to generate? -- JARoman Hi, I think you need dependent list see Debra web http://www.contextures.com/xlDataVal02.html "Xman" wrote: > Is it possible to have a list range in one cell based on t...

How to move CRM databases to different drive
I'm setting up CRM on Small Business Server 2000. The small business server had been originally installed without SQL, so I added SQL 2000 using the small business server installation wizard. I installed SQL in the default location on C:\. I wanted to put the SQL program files on C:\ and the data on D:\, but unfortunately the installation wizard didn't give the option of specifying a different location for the database files. I then ran the CRM installation, which didn't give a choice of database locations either. So I ended up with all the databases on C:\. How do I mo...

Linking fields in different table in same database
I have a database (Access 2007) that is used for holding data on and communicating with FE colleges. Obviously (?) each college has a number of contacts (up to 14) and these are represented in two tables – one for College Details and one for Contact Details which are linked by a one to many relationship. Most colleges have one representative on one of 3 groups, but not all colleges are represented on all groups and some not on any. The membership of a group is noted by a field in the Contact details. Getting a query to run to identify membership of groups is OK. But I can’t fig...

Too big to fit in cell
I have cell A1 with text that is too big to fit in the cell. Standard behavior is that the text of A1 is fully visible spread over A1 and B1, unless B1 is filled. How do I tell Excel that the remains of cell A1 should not be shown in the empty B1. In fact the remains should not be shown at all, so I also dont wan't cel A1 to have 2 lines. Thanks, Lars Hi Lars, You could enter a single quote sign ' in B1. It will not show, and the text in A1 will behaive as you want. Best regards, Per "Lars Brownies" <Lars@Brownies.com> skrev i meddelelsen news:h0dlvg$v3b...

Can I Sum by Cell Colour or Format?
I have a large list of values, these values have been colour coded. I would like to sum each of the colours seperately. I know i can add a filter, sort by colour & sum this for each coulour, but am wondering if there is a formula that would do this for me? Ashley, have a look here http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Ashley" wrote: > I have a large list of values,...

two XP installs on one machine
Hi gang, Can I have a 98SE on C, and /TWO/ different XP installations on two other separate partitions? I have 98SE on C now and XP on E but would like to add a second - different - XP install on another partition. It seems adding one more line to boot.ini should do the job. Any comments? -- Mozart tells us what it's like to be human, Beethoven tells us what it's like to be Beethoven and Bach tells us what it's like to be the universe. - origin unknown "thanatoid" <waiting@the.exit.invalid> wrote in message news:Xns9DA7AC00A9D2Bthan...

Ten Key Calculator cell format
Is there a way to format a cell to move the decimal back two spaces similar to a ten key adding machine. In example, if I key 1025 in a cell can a custom format make that value 10.25. Other examples: Enter Format 10 .10 456 4.56 2000 20.00 Thanks in advance for anyone who could help me with this. Manny ...

Totals of columns
All of a sudden when trying to add or subtract line A, B, C to Column D, Column D is not functioning. I have had no trouble in the past. For example =SUM(H467+F468+G468-E468) To total H468. H468 does not change when I substitute any figure withing the brackets. Check to see if your calc is set to manual. <Tools> <Options> <Calculation> tab, And make sure Automatic *is* checked! -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ----------------------------...

Allow only one user to access workbook at a time
I need to set excel to allow only one user at a time to access a workbook. The workbook is NOT a shared workbook, but it resides on a network share. Multiple users need to access it, but only One at a time. I want all other users to get a message that the file is in use and they can not open it. Is this possible? I believe that is the standard behavior of a network file that is not shared; users who access the file when it is in use get a message the file is in use and whether they want to open as Read Only. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Emir_of_sh...