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


Advertisements:










Sum Of A Column Depending On Data In Another Cell


I want to add up items in column H, but only if Column J says "yes" in the same row.

I tried a few methods as I always try to do, but I came up short with this one.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Sum Data Depending On A Date
I have a workbook full of a bunch of sheets with the same formatting. Each sheet has data for a 2 week period.

I am making a master type sheet and Im hoping to be able to pull some data from each sheet.

On each sheet:
Row 1 has dates starting on E1 and ending on AF1 (several blank/combined columns)
Row 22 has the data I want to pull, and it is one column to the right of the date. So, F22 corresponds with E1

Ex.
One sheet looks like this:
E1 = 8/24/09
AF1 = 9/6/09

I got single amounts to work by using:

=IF((MONTH(E1)=8),F22," ")

However, Im hoping to find a formula that would look at the entire two week period and sum the row 22 amounts for each day that falls within a certain month.

I tried:
=IF((MONTH(E1:AG1)=8),F22:AG22," ")

Which comes back false since not all days in the two week period fall within August (8).

Does anyone have any ideas for a formula that would be easily changeable, and that would pull & sum row 22 data for each row 1 date that falls within a certain month?

View Replies!   View Related
Prevent Cell Change Depending On Sum Result
In my spreadsheet cell g1 is a name and cells g3:g6 contain data for that name.

I have a macro that exports the data in g3:g6 to another workbook and then deletes it.

What I need is a macro that when I try to change g1, gives me a warning if there is unexported data in cells g3:g6 (ie the sum of them is greater than zero) and gives the option of either continuing or stopping (presumably using a YesNo box).

View Replies!   View Related
Have Sum Always In Column C One Cell After The Data
is it possible to have sum always in column C one cell after the data

View Replies!   View Related
Macro - Sum From Last Cell In Column Of Data
I want to got to the last cell with data in column H and turn this into a Sum formula. I want to sum up to H7. Then, I want to copy this sum formula for all columns through to column AH.

I'm thinking it might start with: Range("H" & Rows.Count).End(xlUp).Formula.......but, don't know where to go from here.

View Replies!   View Related
Data Validation Depending On Column
Let's say I have data validation on column A, which only allows 2 values, "Number" and "Letter". Easy enough. If the value "Number" is selected in A1, when the user moves to B1, I want a validation list of 1,2 and 3, when they move to C1, I want a list of 4, 5 and 6 and when they move to D1, a list of 7, 8 and 9. However, if "Letter" is selected in A1, when the user moves to B1, I want a list of R, S and T, when they move to C1, U, V and W, and when they move to D1, a list of X, Y and Z. I tried using dependant validation, but can only get all three columns (B, C and D) to either be 1, 2 and 3, or R, S and T. The validation lists would be existing named ranges on a separate sheet.

View Replies!   View Related
Adding Data In Column A Depending On Data In B
Adding data in column A depending on data in B. I have following macro:

View Replies!   View Related
Pasting Entries Into Sheets Depending On Data On A Column
Let's say I've got nine entries of data. Column A is aligned like this:

One
One
One
Two
Two
Two
Three
Three
Three

What I would like to do is write a macro so that the three entries with "One" in Column A get cut and pasted into a newly-created sheet named "One." All of the Two's get cut and pasted into sheet "Two" and the same thing with the Three's.

View Replies!   View Related
Loop: Macro To Go Through The Table And Preform An Operation In The Corresponding Cell In Column B Depending On The Value In Cell A
I have 2 columns and 10 rows (A1:B10). In column A are certain text values. How do I write a macro to go through the table and preform an operation in the corresponding cell in column B depending on the value in cell A? for example, if A3 = "Complete", overwrite the formula in B3 with the value (paste value)?

View Replies!   View Related
Change Cell Depending On Column Header
I have a spreadsheet with names and start / finish dates columns down the left hand side then a row of week commencing dates along the top.

for each name row I would like to change teh fill colour of a cell to green to represent the week in which they started and to red for the week in which they finished. I therefore need to cross reference the start and finish dates for each name with the relevant week commencing dates at the top. Somehow! I presume there is some kind of vlookup type formula that I need to use in conditional formatting, but I am not sure what.

View Replies!   View Related
Column References: Get A Cell To Return A Value Depending On Whether Or Not A Cloumn Is Populated
get a cell to return a value depending on whether or not a cloumn is populated
for example. If i Have 3 columns EUR, GBP and CHF and there can (99% of the time) only be one entry in any of the 3 on any given row. How then can i get a 4th column to return the value EUR, GBP or CHF on the same row as an entry. I have a relatively complicate nested IF fromula but i think this is slowing down the worksheet as there are 500 or so rows containing it. Ive attached an example segment

View Replies!   View Related
Replace Cell Content- Depending On Other Cell Data
I have a file consisting of two columns, called, "bom ref" and "material"
I need to show the data as indicated in the file, headed required format required.

In effect where I have a 0, that is the material I need to show for every row with a bom ref. of 1,2 or 3, down as far as the next 0 but not including the 0, when I reach the next 0, the material is a new letter, and that letter needs to repeat down as far as the next 0, but not including the 0 and so on.

View Replies!   View Related
Sheet Population Depending On Cell Data
I currently written the following formula, but always have issues when adding additional depending on selection.

D8 is the selection ( Validation ) on PERF EXP FORM Tab, Once you enter the selection... you then go to the Accounts per hour Worksheet
=IF('Perf Exp Form'!$D$8="Test1 14-18 station",IF(('10220DBHE'!B9=A6),IF('10220DBHE'!C9 > 11000,11000,'10220DBHE'!C9),"N/A"),"N/A")

Perf Exp Form
Perf Exp Form *ABCDEFGHIJKLMNOPQ4 *1. GENERAL INFORMATION****************5Customer***Date / Name(s)*6ApplicationSheet Size1UP/2UPOrientationSOG/EOGFold TypeFold Plate# CH***78 1/2 x 142 UPPortraitSOGLetter/ZHeavy1***8Machine / model**Enduro 4-6 station9ConfigurationCHANNEL 1CHANNEL 2CHANNEL 310590-4720-4910 Dual DeckNONENONE11Date / Name(s)*** Excel tables to the web >> Excel Jeanie HTML 4 ......


View Replies!   View Related
Extract Data Depending On Cell Information
i have 20 different materials which have codes assigned to them

wh = white
ivory = ivory
db = duck egg blue

etc etc

when i create an order some of the codes will be called up
i want to be able to put them codes plus the description into the worksheet Ideally into the header. if not call a1


View Replies!   View Related
Show A Certain Cell Depending On Data From A Drop Down Menu.
I have created a color coded calendar on a spreadsheet for work that shows when a book will be published depending on when it comes into our warehouse, this is on sheet one.

I would like on sheet two to have some kind of filter or statement that would eliminate the need to look at the calendar, I.E. the user would just have to type in the date or select the date from a list and excel would show you what day the book would publish on using the data from sheet one.

I can imagine that this is hard to picture so i have attached the file as well.

View Replies!   View Related
Dynamic File Path Depending On Cell Data
I think this is possible but cant find anything on it ........

View Replies!   View Related
Formula For Showing Data Depending On Cell Entry
I need a formula that displays the word "Day" or "Days" depending on cell entry i know the formlula =IF((B12)>0,"Day","") to show the word day but i want it to show "day" if a 1 is entered or "days" if any number over 1 is entered.

View Replies!   View Related
Sum In A Macro (sum One Column After The Last Cell With A Value In It)
I have a Macro and I would like sum one column after the last cell with a value in it. This cell has variable length with each file.

Is ther a way to include this function in my existing Macro?

View Replies!   View Related
Sum Depending On 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 :

A C
23 5
23 12
23 2
45 4
45 1
46 3

in the news the resultat in the same columns its like this
A C
23 19
45 5
46 3

I got this code , but I work only for news colums , A and B

Sub test()
Dim a, i As Long, b(), n As Long
a = ActiveSheet. Range("a1"). CurrentRegion.Resize(, 2).Value
Redim b(1 To UBound(a, 1), 1 To 2)
With CreateObject("Scripting.Dictionary")
.CompareMode = vbBinaryCompare
For i = 1 To UBound(a, 1)
If Not .exists(a(i, 1)) Then
n = n + 1: b(n, 1) = a(i, 1)
.Add a(i, 1), n
End If
b(.Item(a(i, 1)), 2) = Val(b(.Item(a(i, 1)), 2)) + Val(a(i, 2))
Next
End With
ActiveSheet.Range("d1").Resize(n, 2).Value = b
End Sub

View Replies!   View Related
Sum Numbers Depending On Their Source
I need to sum numbers depending on their source. Consider the following sources


Source .....................#
M D11 24 ......... 5
M D11 23 ......... 6
M D02 25 ........ 7

I want sum of all numbers whose source has D11 in its name (Answer 11). What's the best way of doing so?

View Replies!   View Related
Sum Of Values Depending On Color
How can i do sum of the values which are in red color

CODEAMTA1B2C3D4E5F6G7H8

View Replies!   View Related
Sum Depending On Indent Level
I was wondering if it was possible to do a SUMIF function depending on the indent level, e.g. sum all of the values which have an indent of 2 in the row headings.

View Replies!   View Related
Sum/average Even Column Data
How to sum or average the data in the even column ( I mean column 2, 4, 6, etc) in a range (like range A1:DD1)? I tried to use Mod and Iseven to get it but failed.

View Replies!   View Related
Average Column Depending On Values In Another Column
I have tried a few other examples in previous threads that use COUNTIF and ISERROR but I just can't get it right.

Basically I want to average the cells in column J (J2:J6,J8:J12,J14:J18, etc).
My aim here is to determine the average value for each day (Averaging 0 values in this instance is OK).

Now, the date in column A (A2:A6,A8:A12,A14:A18, etc) sometimes returns a blank value, depending on the day of the week (see attached example). This is because, in the full version of the workbook, I have Column A date linked to a perpetual calendar and therefore the dates can change cells depending on the year.

For the Date Cells that return a blank value, I do not want the AVERAGE equation to include the 0 value on these days.

For an example I have attached a month of February where there are blank date cells at either end of the column.

View Replies!   View Related
Looking Up A Sum Of Data In Several Columns For Specific Values Of Another Column
=(SUMIF($J:$J, "Player1",$K:$K)+SUMIF($L:$L,"Player1",$M:$M)+SUMIF($N:$N,"Player1",$O:$O))-SUM($I:$I)

This looks up if Player 1 was in the top 3 scoring zones and adds up the players total score.

I have another column in this table of data labelled 'Number of Players'. I was wondering how to lookup the total score for this player in the games he played under 6 players for example and the games he played in where there were other six players.

I've tried using that formula ^^ with variations of IF and SUMIF formulas to try and figure it out, but I keep getting formula errors or it just returns with the total score, and not just with the total score under 6 players.

I am also trying to create a graph of date on the x axis against score to date on the y axis - everytime I try, the x axis is always numbers instead of the date?

View Replies!   View Related
Sum To Last Used Cell In Column
i would like to sum from from e8 to the last cell in that row and then copy that down to the last row with information in it

View Replies!   View Related
Trying To Sum Every Eighth Cell In A Column
I have a contract expenditure spreadsheet that I use for work (a much shorter example of which is attached) in which I need to add up the values in every 8th cell of a column for a bottomline total. There are too many values to use the SUM function (it's over 30). Both the SUM function and basic addition present the same problem of having to make sure that each cell is included by hand (seriously, my deputy office manager and I go blind trying to make sure it's all correct). It would make my life SO much easier if there was a way to automate it to add every 8th cell so that if I were to add a new contract block, I could just expand a range or something to include the new values.

View Replies!   View Related
Sum Every 3rd Cell In Column
I have a requirement like this... I have values like this in excel sheet

1
2
3
4
5
6
7

I need to create a vba code to add every 3rd row. for eg) 1+4+7+ ... until EOF in the current range.. Can you suggest me how to do a vba coding for that...

View Replies!   View Related
Tally Report Of X: Count On Column B (project $) Depending On The Criteria On Column A (project Description)
I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].

View Replies!   View Related
Insert Sum Formula Across Last Cell Of Each Column
I am making a deposit sheet schedule, which has 10-15 batches of deposits that are entered one after the other, vertically. Each deposit entry can be allocated to many different expense accounts, so a deposit will be 500 with 400 to rent, 50 utitilies, etc. So I need multiple summation rows

So, I want to add a sum row beneath each set of deposits.

My problem is that each deposit is a different amount of rows long.

I was thinking of doing something like this:


'totalRow() is each row where the summation formulas will go totalRow(0) is not an actual sum row, but tells me where the first row of data lies.

For Each cell In Range("H" & (totalRow(x - 1) + 1) & "", "J" & (totalRow(x - 1) + 1) & ":N" & (totalRow(x - 1) + 1) & "", "P" & (totalRow(x - 1) + 1) & ":AB" & (totalRow(x - 1) + 1) & "")
cell.Formula = "=sum(Range(""A"" & (totalRow(x - 1) + 1) & "":A"" & (totalRow(x) - 1)"

Next

I don't know how to reference the ranges I want to sum, because I don't know how to increment the reference to the column (i.e. changing A to B to F to G, etc.) with the Range().Formula construct.

I was thinking of using R1C1 somehow but I'm not as familiar and I don't think you can use r1c1 & Range() at the same time.

View Replies!   View Related
Select Range Depending On A Column
If i have row 10-20 in column A filled, what vba code would i need to select a range in column C that selects rows 10-20

If that 10-20 rows in column A chnages to say 10-50 the vba code will select 10-50 in column C

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 :

A B
23 5
23 12
23 2
45 4
45 1
46 3 in the news the resultat in the same columns its like this
A B
23 19
45 5
46 3


View Replies!   View Related
Set Range Depending On Value In Column D
I have a list of data in colum A and I want to group them together.

In column D I number the group with the same number.

A1 = Tony D1 = 1
A2 = Tom D2 = 1
A3 = Bill D3 = 1

A4 = Helen D4 = 2
A5 = Marg D5 = 2
A6 = Kathy D6 = 2
A7 = Catherine D7 = 2

And so on..... The list up to 100 names

As you can see group 1 = 3 names, group 2 = 4 names, group 3 = 6 or maybe 7 and so on.

I'm trying to set ranges for them Eg:
set rng1 = A1:A3
set rng2 = A4:A7

but it not fixed to set ranges like that. It's variable and changes every week. Next week maybe rng1 = A1:A5, rng2 = A6:A9.

can I have the VB code to determine these variable ranges.

View Replies!   View Related
SUM X Column Range Based On Criteria In Cell
Im trying to do a sumif reading the name in column N (either "Business" or "Procurement"), and suming all values in the range AW:AY.

this is what I have come up with....

sumif(N10:N49, "Business", AW10:AY49)

but it returns incorrect values and not the whole range????

Any ideas? Auto Merged Post Until 24 Hrs Passes;here is an example of what im trying to do...

H2 and H3 are my attempted formulas

View Replies!   View Related
How To Sort A Column Depending On Number Of Letters
i want to sort a column in such a way that it starts with those cells having the highest number of letter. For example:

before:

AA
AAA
A
AAAAAA
AAAA
AAAAAAAAAAA

I want it to look like:

AAAAAAAAAAA
AAAAAA
AAAA
AAA
AA
A

Ofcourse the real list doesnt contain only "A"s. It contains of words and sentences.

How can i sort columns A as mentioned? The order of column A with other columns should not be destroyed be the sorting process.

View Replies!   View Related
Add Or Subtract Depending On Text Value In Another Column
I have a workbook to record the attendance of students. Sheet attendance9 for September's attendance. Enter 1 for present, 0 for absent, L for late, E for early leave and blank cell for no schoolday. Sheet "remark9" is used to record detail of attendance for September. In sheet "attendance9", I want to display in cell AT2 the number of times a student absent for 7 or 14 consecutive days. How to count from E2:AH2?

View Replies!   View Related
Conditional Formatting: Format 3 Columns Depending On Certain Value From Another Column
I want to format 3 columns in excel depending on certain value from another column.
By example: I have the columns A B C completed with some text. On the column D it will be the numbers 0 or 1. If the number is 0 the background color from A,B,C columns shall be Green, if the number is 1 the color shall be red.

I have tried in conditional formatting with the formula =$D$1=0 but the color of the columns are changing only depending on the value from cell D1. I don't know what is the correct formula. For column A1 I want to check the value from D1, A2 - D2, A3 - D3,...,An - Dn.

View Replies!   View Related
Copy Rows To Other Sheets Depending On Values In Column
Ive done a search of the forum for a macro and found some post that seems to be about what I want to do, but unfortunately my minimal vb experience prevents me from adapting them to my requirements.

Ive got a workbook with three sheets; say Sheet A, Sheet B and Sheet C. I want the info in Sheet C copied to either Sheet A or B depending on the info in cells in Column A of Sheet C.

Sheet C contains customer info, there are about 9 column headings and up to 30 000 rows (Individual customers).

The cells in Column A will contain a number between 0 - 23. What I want the macro to do is, if a cell in Column A contains any of these numbers, 0, 1, 2, 6,7,8,9,10,17,19,20,21,22,23, I want that whole row copied to Sheet A and if it contains 3,4,5,11,12,13,14,15,16,18, I want it copied to Sheet B. The cells will only contain one number, never a combination.

View Replies!   View Related
Calculate 10% Or 20% On Investment Form Depending On Interest Rate In Column
The way I have this sheet setup is to calculate a 20% fee off the interest earned column "D". Say you earned 6.5% on a beginning balance so the interest earned is in col "D". This works fine for a 20% fee but I need the fee to calculate a 10% fee if the interest in column "C" hit 4% or below and it also needs to be able to calculate a 20% fee if the interested earned is above 4%.

View Replies!   View Related
Count / Sum Numbers In Column Where Specific Text Exists In Adjacent Cell
One has numbers the other has "YES" or is blank. I want a formula to look at the second column and where there is a "Yes" then count the number in the same row in the first column.

View Replies!   View Related
Sum Empty Cell Will The Above Data
vba macro, I've a s/s where column "W" is named "Proceeds" column and i would like to sum empty cell with above data. see below raw data:

Proceeds
-23,900.00

-18,100.00
198,000.00

198,000.00

-326,250.00
-326,250.00

Now i would like to sum empty cell & colour background with yellow and borders with top and thick bottom border.

View Replies!   View Related
Sum Based On Data In Another Cell
I have data sorted by employee that contains multiple rows for most employees. One of the columns contains a percent. I need to adjust this percent to a revised percent based on the amount of the year elapsed. For example

Jones 25%
Jones 15%
Jones 14%
Smith 30%
Smith 24%

If only 50% of the year has elapsed need to adjust each of the percents for Jones so that they total 50% and do the same for Smith.

I have tried using the offset formula and played with a dynamic named range but am missing something.

Column C contains the correct formula done manually. Need to figure out some way to have the formula change the range that is being summed.


=B2/SUM(OFFSET(B2,0,0,COUNTIF($A$2:$A$11,A2),1))*$F$1

Need to keep the reference cell (B2 in this example above) constant if the name in column a does not change for 2 or 3 more rows.

View Replies!   View Related
Sum Data Between 2 Cell Values
I have a spreadsheet where I am trying to sum all amounts that meet certain criteria (2). Problem that I am running into is that I have several thousand different criteria set rows, each with its own set of criteria (2 per row). The data is on 1 tab and the criteria (along with other information) is listed on another tab so I am trying to return to the criteria tab the total amount from the data tab that meet the criteria by row. I have tried various SUMIF and DSUM formulas and either get weird results or errors.

I am attaching a small sample of the spreadsheet for reference. I need to have in column C on the Criteria tab a sum of all the amounts in column B of the Data tab if column A of the Data tab is between (inclusive of the from and to criteria) column A & column B of the Criteria tab.

View Replies!   View Related
Sum The Sales Revenue Column Of The Attached Per Month Into The Yellow Highlighted Cell @ The Bottom
is there a formula i can use to sum the sales revinue column of the attached per month into the yellow highlighted cell @ the bottom?

View Replies!   View Related
Column SUM Produces #N/A (Vlookup To Find Values That I Want To SUM)
I have a column using Vlookup to find values that I want to SUM.

Some of the look up values produce a #N/A and result in a total sum of #N/A.

How do I get the SUM of a column of numbers when all of the cell values are not in fact numbers.

View Replies!   View Related
Auto Sum Data In First Blank Cell
I have this code here that is supposed to Auto-sum 3 columns for me...

View Replies!   View Related
Extracting Certain Data Depending On Criteria
Have a perfect working spreadsheet, but now need to extract certain bits of data depending on one criteria.

Each row contains data in different columns.

What i need to do is depending on the criteria of a cell (i.e. whether its Y or N) i would like to extract certain cells onto another worksheet all within the smae work book

i.e for example
If i have columns A,B,C,D,E and F and then x amount of rows (as these can change as data is entered) if a Y is entered in F then it extracts the data out of columns A, C and D

View Replies!   View Related
Moving Data Depending On Input
I new for a forum I have a small problem I just stock and cant see what to do. So i need to move data from 4 columns B,C,D,E to F,G,H,I depending on input in column J, like if in J2 i enter letted M (or any from column A) i need to move data from B12,C13,D13,E13 to F2,G2,H2,I2 and so on. I dont have experiens in programming in exel or using formulas.

View Replies!   View Related
Calculating Several Data Depending On Date
I have a truck log. Every time some comes in to take a truck they must log the truck out with the millage. After a week, I am supposed to find out how many miles my trucks ran for that week.
What would be the code to....

In K column, calculate the milage that is in row F; which is A3 - A2 and so on down. But it would have to skip a line every time the truck number changes (in column C) it would skip that line. It would go until a blank cell.

Does anyone have any ideas how to do that?

View Replies!   View Related
Find Data Depending On Two Criteria
I Have want two find the dates and the data of these dates
depending on the two criteria i have

note"the two criteria would be left(b3,4) and right (b3,4)

ex
in f3"date" i want to find the dates correspond with these two criteria
and then find the data of these dates in G3 Depending on the two criteria too.

Sheet1

ABCDEFG2DateData
Criteria1Criteria2DateResult31/1/2008M111 GG1G M113
M191M1131/2/2008M191 G1GG M11341/2/2008M191 G1GG M113

1/5/2008M191 G78GG M11351/2/2008M111 GG1G M113

1/8/2008M191 G1G9G9 M11361/3/2008M141 GG5G M113

71/4/2008M118 GG6G M113

81/5/2008M191 G78GG M113

91/5/2008M141 GG5G M113

101/6/2008M111 GG2G M113

View Replies!   View Related
Formula To Change Cell Colour :: Shade A Cell Depending On Condition
Is it possible to use a formula to shade a cell dependent on a condition? I have tried an if formula (see below) but it is incorrect. =if('November 2006 SVOC'!B6>'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [red],if('November 2006 SVOC'!B6<'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [blue],))

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved