Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Sum The Totals On One Column, If Two Other Columns Are True

(Excel 2003 on windows XP.)

I need to SUM the cells in column J when column E has the word "yes" and the range N:R has a letter "w" in one of the columns. Only one of the N:R columns will have an entry on any one row and it may not necessarily be a "W".

I have Googled, and being a beginner I don't comprehend the result! Some people were talking about using an add in, but I won't be allowed to add software to our PC's.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Add Like Text Totals Of Columns At Bottom Of Column
Note that the "Fruit" and "Fruit Totals" column letter position will always be different every time I run the macro, so I would like the macro to find these columns by name rather than by column letter.

I'd like a macro that adds the total number of fruits in the "Fruit" column by looking doing 2 things...

1. Add the total number of "Fruits" in the "Fruit" column, find the bottom-most row of data in the "Fruit" column, go down 2 rows, and create the text named "Fruits Total"

2. In the cell directly to the right of "Fruits Total" put the value of the total number of fruits in this cell (which will be in the "Fruit Totals" column)

3. Add the total number of "Apples" in the "Fruit" column, find the bottom-most row of data in the "Fruit" column, go down 2 rows, and create the text named "Apples Total"

4. In the cell directly to the right of "Apples Total" put the value of the total number of apples in this cell (which will also be in the "Fruit Totals" column)

5. Repeat steps 3 & 4 for the other fruits

Best to show you an example ...

View Replies!   View Related
Selecting Totals From UNHIDDEN Columns Only
I have a workbook that has 2 sheets; "Quote Summary" and "ECS". The "ECS" sheet contains columns "C" through "L" each of which has a total on row "27". At any time, one or several (but never All) of these columns may be unused and therefore hidden.

I would like to know if it is possible to sum the total of only those columns that are NOT hidden and display that value in cel "C15" of the "Quote_Summary" sheet.

View Replies!   View Related
Column Totals
I need help to total a column but in four cells the values are as a result of conditional formatting and the total ignores those cells. Is this difficult or am I a bit slow? This is in 2003.

View Replies!   View Related
Comparing 2 Or More Columns And Returning A True Value
I'm quite new to array formulas in excel and was wondering whether or not I can advance on this formula:


The problem is I would like to compare the entire colums (or array) and return the number of 'TRUE' statements in the array as a value.


however, this returns all values as 'FALSE'

example of arrgument:

IF C5:C65535=customer then count how many cells in K5:K65536=completed

View Replies!   View Related
Dynamic Column Sum Totals - Anyone??
I have a column of figures and a formula that displays the sum. My issue is that I occassionally insert more rows with more figures but obviously the sum doesn't include this new figure as the range is static. I guess I'm looking for a way to dynamically perform sums in a column.

View Replies!   View Related
Find Identical Item, Look For True 2 Columns Over
I’m putting together a blackjack odds calculator. I could use some help with a formula. If I have an A,7 (ace low) it needs to return a 0 (or false) for a “Possible Hand” because further down the column A,7 (ace hi) has a true “End Hand Total”

I was going to use an IF and a VLOOKUP (looking lower on the column), but then I cannot sort the column. Is there a formula that will find the hand and check for true 2 columns over from identical hand. There are 169 starting hand combinations. The attached worksheet only has 31 – for simplification.

View Replies!   View Related
Pivot Tables And Counting True Fields For Two Columns
I have just started using pivot tables and I have ran into a problem with the count feature. I have an original data list that breaks down in the pivot table to:

events on that date

Using the pivot table facilities, I want to do a count of those who said they were going to attend, and those who actually attended. The detailing both of these columns on the original list have a true false designation. When I do the count, the summarised information only counts the number of records for each of these events, and not the 'true' field entries for each of these columns.

How do I count these two parameters for 'true' from the original list in the pivot table so I can see a comparison between these two numbers?

View Replies!   View Related
Find The Matching Text TRUE/FEE Or FALSE/FEE In Columns
I have 2 sheets. On one sheet, this column J2:J490 contains text that either says "TRUE" or "FALSE" and another column AJ2:AJ490, text that says "FEES". (many of the other cells in this column say something else). I need a formula that can find the matching text TRUE/FEE or FALSE/FEE in these columns, count them, and give me the product.

View Replies!   View Related
Stacked Column Chart Totals 2007
Would like to add totals to stacked column chart in Excel 2007.

I have followed same steps listed here [url] but cannot position data labels above column.

Excel only gives me the options Center, Inside End, and Inside Base.

I also tried setting label position with VBA:

For Each co In ChartObjects
For Each s In co.Chart.SeriesCollection
If s. Name = "Totals" Then
For Each dl In s.DataLabels
dl.Position = xlLabelPositionOutsideEnd
Next dl
End If
Next s
Next co

Also tried the following without luck:

dl.Position = xlLabelPositionAbove

For each VBA example I get following error:
Run-time error '-2147467259 (80004005)':
Automation error
Unspecified error

It seems like Microsoft has removed this positioning option for stacked column charts because it works fine for normal column charts. Anyone find a way to do this otherwise?

View Replies!   View Related
Running Totals In One Column With Data Added From The Columnn Before It
I am trying to do a running total in one column with data added from the columnn before it. But I dont want the last amount in the running total to continue down the page I would like it to be either blank or zero amounts. Also if there is a zero amount in the first column the running total should show the amount previous.

View Replies!   View Related
Record The Total Items From Each Sheet In A Column Called All Totals
I am a bit stuck with an excel spreadsheet I do for work and would like to ask your advise, I will try to explain as best as I can.
I am running a small store that feeds 13 buildings.

On the spreadsheet there are 13 sheets, one for each building.
The items will go out monthly.

What I need to do is record the total items from each sheet in a column called All Totals on the first sheet, this will add up the totals for all the buildings.

If I put an attachment that may help out a bit.
If you can look at P3 it is all the P3s to add together, next down will be total of P4,P5,P6.

View Replies!   View Related
Hide Checkbox TRUE/FALSE & Sum Values Adjacent To TRUE
I have a worksheet where I have around 300 rows, each with 7 columns. What I want to do is add a checkbox to each column. I plan on setting non-applicable checkboxes to mixed status and locking the worksheet. I will unlock applicable checkboxes and sumif or countif their value according to row-based scoring, for example, each checked checkbox represents a value of 3. I do not know VBA and have chose to use the form control checkboxes rather than ActiveX.

I believe that a formula for this would be something like: =SUMIF(B1:B3,True,"3") or =COUNTIF($B$1:$B$3,True)*3

I am wondering firstly if I have that right and secondly if there is a way to stop my checkboxes from displaying labels. Currently, if I click on one it displays True behind the active checkbox. If I uncheck it, it displays False.

View Replies!   View Related
Solve Counting Result One Col If Criteria In Another Column True.
I am trying to create a formula to count the number of time the word "NEW" appears in one column, provided the result of another column gives a certain answer. Sample of my problem and question in detail on attached.

View Replies!   View Related
Data Validation: If Previous Column Is Having A Value, Then Return FALSE Else TRUE
I am applying data validation in my workbook. The data in ColS consist of:

(Row2) X
(Row3) Y
(Row4) Y
(Row5) Y
(Row6) X

In ColT, I am to apply data validation: i.e. If in concerned row of ColS is having "X" value, then I am to show an error message. Else, values from A2:A5 should be shown in the as list and these should be accepted in ColT.

View Replies!   View Related
Last Ocurance Of The Last Date Used For Each Month And Then Use The Cell Number To Calculate The Column Totals For That Month
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year

Column A is for date input and the same date can be repeated several tumes :-

1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09

Sometimes there are all 30 /31 days but normally not .

I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.

View Replies!   View Related
Count Of TRUE & FALSE And Assign 1 To True And Yes And 0 To False And No When I Total The Rows
I am trying to Sum lines of info with "True or False" and "Yes and No". I would like to assign 1 to True and Yes and 0 to False and No when I total the rows. Never tried this in Excel, on Lotus and the formula does not work. I can find and replace, but I would like to be able to use a formula.

View Replies!   View Related
Cell B1 To Display True If Any Of The Values In Column C Match Otherwise I Want To Display FalseOR Function Confusion

i m wanting Cell B1 to display true if any of the values in column C match otherwise I want to display false.

A 1
B 0
C 0
D 0

But I can't determine how to get this done.

View Replies!   View Related
Column 9 To Be The Sum Of Columns 3, 6, 7, And 8
I need column 6 to show 15% of column 5, then I need column 9 to be the sum of columns 3, 6, 7, and 8. I just can't figure out how to do this. What I'm trying to do is take a wholesale price, get 15% of it to figure for shipping, then add the wholesale price, shipping charge, a drop ship charge, then my Paypal charge together so I can see what I am paying to ship a specific item...then I can figure out what to charge.

View Replies!   View Related
Add Totals By Day
Here's what I have...

Column A Column G
date $ Amount

my data example...

10/5/2008 $10.00
10/5/2008 $20.00
10/8/2008 $12.00
10/8/2008 $8.00
10/8/2008 $25.00
10/9/2008 $75.00

What I want the formula to do is look in Column A find all of the dates that are on the same day and then look in column G and add all of those $ amounts.

So the result would be...

10/5/2008 $30.00
10/8/2008 $45.00
10/9/2008 $75.00

View Replies!   View Related
Splitting One Column Into Many Other Columns
I need a macro which place chosen parts from one column into another column.

For example I have following text in column E:

Column E
Flugschneise {f}
(total) im Arsch [vulg.]
Geld und Gut
Pensum {n}

*chosen content not always at the end of a cell!

Now i need a macro which puts chosen content from Column E to Column F or Column G. For example I want that all “{f}s” should be deleted from column E and put into column E. Or I want all {n} to be deleted and placed into column F. Or I want all [vulg.] to be deleted from column E and put into column G. If column F or G already consist of text then the new text( {f} or {n} or [vulg.] ...) should be added to the existing text but separated through a separator like comma or semicolon …

It means that the macro is each time fed with information about what to delete from which column and where to put it. It would be cool if a simple msg box would ask for the needed information.

View Replies!   View Related
Insert New Column After Every 2 Columns
I have many excel sheets with many columns. I want to insert new columns but after every 2 columns, starting from column M.

View Replies!   View Related
Combining Two Columns To 1 Column
see the picture below to get an idea of what i want to do.

View Replies!   View Related
Sum Column If Criteria In Other Columns Are Met
I am trying to create a formula that will sum up a value in one column if certain critieria residing in other columns are met. I have attached a sample and the criteria.

View Replies!   View Related
Insert A Column Every X Columns...
I am looking for a VBA code (or alternatively a way of doing this in normal Excel) that can let me insert a column every X columns (for example every six columns).

Ideally I would want a code that can let me insert a specific column (the same) every X columns, but a formula that inserts a blank column every X columns will hopefully do.

View Replies!   View Related
Add Two Columns Together And Then Divide The Sum By Another Column
I may not even be calling it the right thing. but let me explain what i'm looking to do. i've got three columns on a spreadsheet and i need to be able to add two columns together and then divide the sum by another column. so something like this:

4+4/636 = 79.5

d2+f2 (divided by) h2 = 79.5

View Replies!   View Related
Inset 2 New Columns In Next Available Column.
I have had a search around but cannot find exactly what I'm looking for. I have a worksheet with data from B5-E10 and G5-J10. I need a macro which will insert two columns after column E. So then it would push what was in G5-J10 to I5-L10.

The macro would need to look for the blank column (column F) and then insert two columns before it. The columns will be constantly changing so that why it needs to look for that blank column. After they have been inserted would it be possible to give the columns headings such as 'New column 1' and 'New column 2' in row 4.

View Replies!   View Related
Combine Two Dynamic Columns Into One Column
i need to take all cells in column A with values and all cells in column B with values and combine them into column C so i can sort and subtotal. Columns A and B may have 2 values or 2000 values.

View Replies!   View Related
Summe Column Depending Of Other Columns
I want to create a macro that make the somme of 2 other columns when the nummer of the first column ist double(more than one ) and put the somme in a new Rows. like this :

23 5
23 12
23 2
45 4
45 1
46 3 in the news the resultat in the same columns it΄s like this
23 19
45 5
46 3

View Replies!   View Related
Split A Column Into Multiple Columns
The spreadsheet contains over 21,000 rows of data, and one of the columns (D I think) contains data as in the two examples below.

What she wants is to split this column at the semi-colons ( and have the column header as the "field" name.

Unfortunately not all the cells have the same number of "fields" as you can see. Some don't have an "addressLineTwo" while others also have "stateprovince".

Is it possible to split the column so each "field" goes into it's own column?

Please note that if a "field" is missing there is not two semi-colons to indicate an empty field. I'm also fairly certain that, between them the two examples below show all possible fields.

Data Examples.

addressLineOne:Road Belen Staana;addressLineTwo:Costado Oeste;city:SAN ANTONIO DE BELEN;highRate:194;latitude:9.97631;longitude:-84.20038;postal4005

addressLineOne:1766 Homestead Drive;airportCode:ROA;city:HOT SPRINGS;highRate:500;latitude:37.99662;longitude:-79.83079;postal24445;Rating:52;stateprovince:US

Didn't there used to be a "Split" function that split text over two cells? I'm sure I used it years ago, but can't find any mention of it in Excel 2003.

View Replies!   View Related
Hide Columns On Column Labels
I was wondering how I might be able to hide columns based on their 1st row labels with vba - I never know where these columns might be on different file I get but I want to hide the same columns every time. right now I am just specifying column numbers or letters but it's not working out:

Columns("A:C").EntireColumn.Hidden = True
Columns("G:I").EntireColumn.Hidden = True
Columns("K:R").EntireColumn.Hidden = True
Columns("T:V").EntireColumn.Hidden = True

Columns("A:C").EntireColumn.Hidden = False
Columns("G:I").EntireColumn.Hidden = False
Columns("K:R").EntireColumn.Hidden = False
Columns("T:V").EntireColumn.Hidden = False

View Replies!   View Related
Average The Difference Of 2 Columns W/out A 3rd Column
I have two columns:


1 10
2 9
3 8
4 7
5 6
6 5
7 4
8 3
9 2
10 1

I need a formula that takes the difference of the two columns (1-10, 2-9, etc.) and then finds the average for all of the differences. I don't want to have a third column with the differences and then take the average of those.

View Replies!   View Related
Unlock Columns If Column Is Not Blank In Any Row
Unlock columns if Column is not blank in any row

Using VBA
On Sheet("Draft Final") , search every row and

If Col A is not blank, then unlock Columns G: M and unlock Columns O:U

View Replies!   View Related
Create Separate Columns From One Column
I have a spreadsheet with these columns:Group_Name
Contact Name
Company Name
Contact ID

The Group Name Column has the name of the group that the Contact Name is a member of. The Contact Name could be a member of many Group Names and therefore there may be many rows of the same Contact Name one with each Group Name that they are a member of. I have manually created separate columns for each Group Name in the spreadsheet that is attached.

I would like to have a macro that will look at the Group Name column and create a new column for each distinct group. I then would like the macro to go down the Contact Name column (I believe that I will have to have it sorted)and look at the Group Name that is listed in that row and put "True" in the Group Column that was created in the above step. Then, go to the next row, if the Contact Name is the same, look that the Group Name column and put "True" in the corresponding Group Column in the FIRST ROW OF THAT CONTACT NAME. Then Delete that row.

View Replies!   View Related
Unhiding Columns (column A That You Can't Click On!)
I have columns A to N hidden and need to reveal them. I know you can manually drag each one to reveal it. But I thought there was a way through highlighting/menu options to reveal them all instantly.

View Replies!   View Related
Copy All Columns Data In One Column
I want a macro that copies all columns data in coulmn a which starts from row 2 and delete rest of columns data.

see mentioned below example....

View Replies!   View Related
Split Data In One Column Into Two Columns
For each cell in column A, I am trying to split the data between two new cells. The data in each cell is separated by a “|”. All information to the left of “|” should go in column B. All information to the right of “|” should go in column C.

Column A
Aa3 |AAA
A3 |AA-
A2 |A
A3 |BBB+
Aa3 |AA
Aa2 |AA+

Desired Results
Column B………….Column C

View Replies!   View Related
Count Duplicates In 2 Columns, But Not If Column A=b
I have data in two columns where column A is a name and column B might be a synonym or might just equal the value in column A. I want to count duplicates if cell A2 matches any other cell in column A or B, unless the only match is with cell B2. If cell A2=B2, but also matches cell A7 then it should be counted as a duplicate. I've searched through a lot of the threads and found a lot of useful information, but nothing that seems to do what I need.

View Replies!   View Related
Macro: Transpose 1 Column Into 3 Columns
I am trying to transpose 1 column into 3 columns:




Here is the code I have:...................

View Replies!   View Related
Transpose Each Row Of X Columns Into Single Column
I need to combine data in 5 columns into 1 column as below. The columns to be merged are always identical in length up 20 000 lines.(Full example attached)
Raw data in columns A,B,C,D,E as below

A1 B1 C1 D1 E1
A2 B2 C2 D2 E2
Converted should be

View Replies!   View Related
Calculate Totals
I have a spread sheet that is used to review calls placed by a call center.

Column A has the extensions of the phones, and Column E has the type of call (Outgoing or Incoming). Each line is a new phone call.

We have about 8 extensions, but the worksheet could have a couple thousand calls. So, Column A could have extension 1401 from rows 1-100 as extension 1401 made 100 calls. I'm looking for a formula or macro that will summarize how many outgoing and incoming calls extension 1401 had. Thoughts?

My initial thought was something like this:


In this formula, I would type in the extension of H2 and it would scan Column A and add up the values in Column E. The only problem is, that Column H doesn't contain a numerical value. It only has "Incoming" and "Outgoing" (minus the quotes), so this doesn't work.

View Replies!   View Related
Sum Totals By Date
I would like to get the total in column B but its not giving me the correct total. So I would half to use a helper column in column D to retrieve correct results. Is there anyway I could have an all in one formula for this. I would prefer not to use the helper column. The correct result is in cell D23.

View Replies!   View Related
Adding Up Totals
Im trying add up totals on a list


So for instance i want it to look at column A and find all the a's then then add the value of column b
so the total here would come out as 35

View Replies!   View Related
Sum Totals From Another Worksheet When (a And (b Or C))
I need and can't wrap my head around it enough to figure it out myself.

On one worksheet, I have raw sales data:

Cust # Cust Name Product # Prod Name Sales Date
10001 whoever 800001 whatever 200 1/3/2009
10003 whoever2 800241 whatever 250 1/3/2009
10001 whoever 800060 whatever1 210 1/3/2009
10001 whoever 800055 whatever2 222 1/3/2009
Each product has multiple product numbers, so one product could have multiple product numbers: 800001, 800055, 800241, 800002, etc..

I want to count total sales by product, regardless of customer for a certain date.

I have the product numbers in their own cells.

I would like to say "If the product number is any of these, and the date is this, add all the sales numbers together and give me a total"

View Replies!   View Related
Tallying Totals Of Ten
I have a column of wickets (M). M5 and M6 are the number of wickets for one match, M7 and M8 for the next, and so on. Two wicket entries for each match.

I needed a formula to count the amount of times the combined wicket total for a match is greater than 10. Initially there were only a few M values, so I used this:

IF(M5+M6>=10,1,0)+IF(M7+M8>=10,1,0)+... and so on. Now I want to expand it to have more M values and this formula would become huge.

View Replies!   View Related
Add Totals By Week
Does Excel know week numbers? e.g. Week 40 is Sunday 10/5/2008 to Saturday 10/11/2008. Here's what I have...

Column A Column G
date $ Amount

my data example...

10/5/2008 $10.00
10/5/2008 $20.00
10/8/2008 $12.00
10/11/2008 $8.00
10/12/2008 $25.00
10/13/2008 $75.00

What I want the formula to do is look in Column A find all of the dates that are in week # whatever and then look in column G and add all of those $ amounts. So the result would be...

Week 40 $50.00
Week 41 $100.00

View Replies!   View Related
Calculating Totals
I am attempting to build a spreadsheet for work, and I'm having trouble calculating a correct total.

In essence, the bit of my spreadsheet I'm concentrating on is 2 columns:

Column A is headed "Type of Work" and Column B is headed "Completed".

The Type of Work will either be Letters or Memos, and the Completed will either be Yes or left blank to indicate No. For other reasons, No has to be represented by a blank cell.

At the moment, I have 12 rows, going from A2 to B13, with data such as the following:

Letters Yes
Letters Yes
Letters Yes
Letters Yes
Memos Yes
Memos Yes

I need a summary section at the side with the following calculations:

Total Number of Entries
Total Number Outstanding
Total Number of Letters Outstanding
Total Number of Memos Outstanding

However, as I have only populated it with 12 rows of data for my testing purposes, I need to build formulas to take into account the fact that a maximum of 2000 rows may be filled in by other people over the next few months. Therefore, my formulas look like the following:

Total Number of Entries (stored in E2):

Total Number Outstanding (stored in F2):

Both of the above formulas work perfectly, and when I enter a 13th and 14th row, the totals update as I want them to, so I have no problems there.

However, I am struggling to enter a correct formula which will calculate the total number of Letters that aren't Completed.

I did set some names and tried to use:

but it gave me a #NA error, presumably because the named range was looking at all the empty cells up to A2000.

View Replies!   View Related
Cumulative Totals
This problem has come at the end of a big exercise whereby I've managed to construct a spreadsheet that automates three payment processes based on various VLookups to another file. However, I've come to a stage where I need to pre-empt a payment allocation based on cumulative totals.

I'll try and explain clearly below but I've also attached a spreadsheet showing the intended result (along with a copy without the output so that someone can add in the formula - again, if it exists.

So, this all centres around a code allocated to our clients and a declaration that they complete. Say client code "Apple1" (col E) sends in instructions to pay on a position of "650,000" (col F). Within our existing spreadsheet we have an available position of "800,000" which can be seen by totalling column G for client code "Apple 1".

So that's the first requirement - for all rows on my existing spreadsheet for client code "Apple1", I want to add the cumulative total of column G ("800,000") I'd like this captured in column H, as per my example.

We can then make payment based on their declaration, up to their total available amount. However, we need to pay them in stages according to our existing allocations (col G). Therefore, taking the first example, their declaration show's an available position of 650,000, their total position is 800,000 and the available nominal amount for that row (col G) is 200,000 - so they can be paid on that full amount therefore, 200,000 should be shown in cell I3.

So now, they've got 450,000 left to be paid and cell G4 again shows a position of 200,000, so once again, cell I4 should show 200,000.

The client "Apple1" now has a payment amount remaining of 250,000 remaining but in this instance, cell G5 is for 300,000 - so because the remainder of their available payment is less than the amount in cell G5, we should post the available payment amount in that field - 250000.

Therefore, if you add up all of column I for client "Apple1" it comes to 650,000 - the amount we have on our declaration - even though the total of their available position is 800,000.

I've included another client in the mix "Sauce2" who should show 200,000 and 0 respectively in cells I6 and I7.

View Replies!   View Related
Totaling Sub Totals
Let me first explain what my worksheet looks like.

View Replies!   View Related
Having Different Totals From Different Options
I would like to know how do I go about adding the following:

This is a test it counts out of 1250 points

But there are 3 fields which can be omitted from the grand total of 1250
Some students might not have all three fields. The fields totals are 30, 90 and 130. The students might have one or two of them in either order. Now what I would like to do is have 3 different fields where I can mark with a Y=Yes and N=No in a block. then it would change the Grand Total score accordingly.

View Replies!   View Related
Merge Columns B,C,D,E And F, If Duplicates Exist In Column A
i have got a wordlist in worksheet "original" which looks like:


View Replies!   View Related
Copyright © 2005-08, All rights reserved