Hello All,

I need a cell formula that sums up specific cells based on Current Qtr. For example, I am collecting data each month on sales, shipments and forecast. In one cell I want it to look at the current Qtr and choose the correct group of Cells to Add Up based on current quarter.

I know this isn't a great explanation, but the attached spreadsheed should help explain what I am looking for. i appreciate any help that can be provided. Thanks

Kelly

I need a cell formula that sums up specific cells based on Current Qtr. For example, I am collecting data each month on sales, shipments and forecast. In one cell I want it to look at the current Qtr and choose the correct group of Cells to Add Up based on current quarter.

I know this isn't a great explanation, but the attached spreadsheed should help explain what I am looking for. i appreciate any help that can be provided. Thanks

Kelly

- Sum Cells Based on Current Qtr (Part 2)
- Count Sum Cells Based on Colour & Content
- Highlight a (different) cell based on current active cell
- Sum Cells Based On Color Index
- Pick a cell condition base on current month
- Changing the format of a cell based on how it's contents (date/time) compare to current system time...with twist
- Sum values based on cell colour
- UDF will Avearge a range of cells based on their fill colour
- Sum amount based on current month
- Sum cells based on whether another cell has a value, then repeat?
- Sum Based On Cell Color After Sort
- Making a cell change background color based on if positive or negative sum total
- Changing Date in cell based on current date in another cell
- Summing the difference of multiple cells based on the value of a cell
- Hide Row of Cell Based on Cell Color
- Formula for summing a range of cells based on cell color?
- Sum cells based on color in Mac 2008
- Conditionally sum cells based on time criteria
- Changing the content of a cell based on its value
- Sum cells based on date criteria
- TRUE FALSE In Cells Based On Selection
- Sum cells based on each change in value in other column
- Delete Cells Based On Fill Color
- Insert formulas into cells based on criteria

OK, I got a lot of great help earlier on what was a simple question apparently. Hopefully this one is too.

The attached worksheet shows rows of numbers that I need to add up by month. However, I have a delinquent qty that I need to add in only during the current Qtr.

For instance, during Q1 I want the Deliquent to be added in only for Q1 but not for Q2 through Q4, but I want Q2 to still add Apr, May and June data.

I hope that this makes sense. The attached spreadsheet should help clarify some.

Thanks

Kelly

Has anybody written a function to achieve this?

Many Thanks Finny

On the same sheet I have two data areas identical in size a1:o15 and a16:o30

First area has data which I need to look at while moving through second area so ...

I want to highlight the (logically) linked cell based on current cell (which will be in the second area).

i.e. if I move into cell B18, I want cell B3 to change fill colour while I'm in the cell B18

(then I move to D19 and want D4 highlighted etc).

I've tried mucking around with conditional formatting and using CELL "address" with INDIRECT and ADDRESS but can't get close.

As an intermediate step, I've got a Worksheet_SelectionChange sub updating a fixed cell with the current cell address but don't seem to be able to use that info.

Would very much appreciate help

Thanks, Ian

Dave's original code is

VB:My modified version is) Dim rCell As Range Dim lCol As Long Dim vResult '''''''''''''''''''''''''''''''''''''' 'Written by Ozgrid Business Applications 'www.ozgrid.com 'Sums or counts cells based on a specified fill color. ''''''''''''''''''''''''''''''''''''''' lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell,vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:This modified version compiles without error.) Dim rCell As Range Dim lCol As Long Dim vResult '''''''''''''''''''''''''''''''''''''' 'Written by Ozgrid Business Applications 'www.ozgrid.com 'Sums or counts cells based on a specified fill color. ''''''''''''''''''''''''''''''''''''''' lCol = 8 If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell,vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

My problem is setting the range to be checked, as the range itself changes upon each new dataset being entered. my current code to call is:

VB:where X is the result of an xlup.row request. It is always erroring with a type mismatch.)If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks to all in advance for the help

If today March 2, 2012, the cell should pick up cell C1, if the date becomes april it should pick up the C2 and it continues for future months (C3....C100)..

Thank you in advance and more power.

I've been a frequent visitor to the site, and have found the answer to most of my questions. Thanks for your help.

But I've got one that i'm stuck on. I maintain a workbook with a list of projects and esitmated completion dates and times (to the minute). I would like to change the interior color of the cell based on current local time. Basically, if the dead line has been passed change the cell background to yellow. A long list of dates and times tend to blur together after awhile, this would help it stick out.

The kicker is that the date format is non-standard. The date format is [ DD MMM hhmm ], so 21 August, 2008 10:15 PM looks like: 21 AUG 2215. It is safe to assume the year is the same as current. Another problem is that I would have to keep the format exactly the way it is and all in one cell.

I've thought about chopping it up with Left and Right functions in VBA, then compare it with IF/THEN statments to determine the actual date. As in:

IF xxxx = "AUG" then yyyy = "8"

Then build it back up and convert to date with CDate. I'll then compare that to the system time using the Now function. I'll run a loop of some sort do all the lines (probably a For/Next loop). Although I'm pretty sure that I can get it to work, it is pretty complex for something that seems to be pretty simple. I figure i'm missing something, maybe a shortcut.

I'm sorry i can't put any real code or formulas on here, I'm still rolling it around in my head. Any help, pointers or tricks would be greatly appreciated. Thanks in adavance.

Can you sum cells based on colour, and if so, how do you determine what code/number your colour is to input into the programming?

Thanks!

The function on that ozgrid page is ...

VB:Range) '''''''''''''''''''''''''''''''''''''' 'Written by Ozgrid Business Applications 'www.ozgrid.com 'Sums cells based on a specified fill color. ''''''''''''''''''''''''''''''''''''''' Dim rCell As Range Dim iCol As Integer Dim vResult iCol = rColor.Interior.ColorIndex For Each rCell In rSumRange If rCell.Interior.ColorIndex = iCol Then vResult = WorksheetFunction.Sum(rCell) + vResult End If Next rCell SumColor = vResult End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I used the formula below, but it returns a zero. Did i have it right?

=SUMIF(H1:H100,"="&MONTH(TODAY()),I1:I100)

----------------------------------------------------

Date | Total Amount

23/10/2009 18:15:57 | $1,288.00

23/10/2009 18:16:00 | $1,288.00

In a nutshell, I want to put a formula in column B that will sum numbers in column G based on whether there's a number in column D, and repeat that for each time a number appears in column D. A workbook is attached.

Column A lists members; members may have more than one user associated with their membership.

Column C lists account numbers; there is one account number per member. However, column C also lists the birthdate of each user where the user name appears in column A.

Column D lists the year-to-date transaction total. This is the only value in column D and it appears on the first line of that member's list of transactions.

Column G lists the transaction amounts for each user.

In the attached workbook, G2, G5, and G8 are transactions for Jane Public (a user on Joe Public's membership); G11 and G14 are transactions for Joe Public); and G17 and G20 are transactions for Sue Public (a user on Joe Public's membership). G23 is the transaction for Doug Doe. G26 and G29 are transactions for Betty Smith (a user on John Smith's membership); G32 and G35 are transactions for John Smith.

I want, if possible, to put a formula in B2 that says "If D2 has a value in it, sum the cells in column G starting with row 2 and as long as the cells below D2 are blank and display that sum here. Don't display anything in B2 if D2 is blank. Repeat this when the next value in D is encountered."

So if I put that formula in B2, it would see that there's a formula in D2 and display, in B2, the sum of G2:G22 (transactions for the Joe Public membership, which incorporate the transactions for Jane, Joe and Sue). If I put it in B3, it would display nothing in B3 because there's no value in D3. If I put it in B23, it would see that there's a formula in D23 and display, in B23, the sum of G23:G25 (transactions for the Doug Doe membership).

I would expect to find B2 showing $42.00, B23 showing $3.00, and B26 showing $911.00.

Is there a way to do this with a formula that I can put in B2 and copy down the column? Currently I'm manually putting =SUM(G2:G22) in B2, =SUM(G23:G25) in B23, etc. and as it's a lengthy report it's pretty tedious. Any solutions or suggestions much appreciated!

VB:Cell C2 contains this formula for the following UDF (C1 has the fill color being evaluated by the UDF)Range Dim FiledRange As Range Dim PaidRange As Range Set FiledRange = Range("c4:c100") Set PaidRange = Range("d4:d100") For Each FileDate In FiledRange.Cells If DateDiff("d", FileDate, Date) > 90 And FileDate.Offset(0, 1).Value = "" And _ FileDate.Value "" Then FileDate.Offset(0, 2).Interior.ColorIndex = 3 'Red ElseIf DateDiff("d", FileDate, Date) > 60 And FileDate.Offset(0, 1).Value = "" And _ FileDate.Value "" Then FileDate.Offset(0, 2).Interior.ColorIndex = 6 'Yellow ElseIf DateDiff("d", FileDate, Date) > 30 And FileDate.Offset(0, 1).Value = "" And _ FileDate.Value "" Then FileDate.Offset(0, 2).Interior.ColorIndex = 4 'Green Else FileDate.Interior.ColorIndex = -4142 End If Next FileDateIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

=SumColor(C1,E4:E100)

VB:When a date is entered in Column D, the cell that was colored in column E is then set to clear and the adjacent cell in column F is set with that color instead. Following is a code sample for that.Range) '''''''''''''''''''''''''''''''''''''' 'Written by Ozgrid Business Applications 'www.ozgrid.com 'Sums cells based on a specified fill color. ''''''''''''''''''''''''''''''''''''''' Dim rCell As Range Dim iCol As Integer Dim vResult iCol = rColor.Interior.ColorIndex For Each rCell In rSumRange If rCell.Interior.ColorIndex = iCol Then vResult = WorksheetFunction.Sum(rCell) + vResult End If Next rCell SumColor = vResult End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:Everything in columns A through H is then sorted based on the reimbursement dates in column D. This is when I get the #VALUE! error in C2Set DatePaid = Range(ActiveCell.Address) Set DaysTilPaid = Range(ActiveCell.Address).Offset(0, 2) Set DateFiled = Range(ActiveCell.Address).Offset(0, -1) Set AmountFiled = Range(ActiveCell.Address).Offset(0, 1) If ActiveCell.Value = "" Then DaysTilPaid.Value = "" DaysTilPaid.Interior.ColorIndex = -4142 ElseIf DateDiff("d", DateFiled, DatePaid) > 90 Then DaysTilPaid.Interior.ColorIndex = 3 'Red DateFiled.Interior.ColorIndex = -4142 AmountFiled.Interior.ColorIndex = -4142 DaysTilPaid.Value = DatePaid - DateFiledIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:Is there any way to avoid the error? Or is there a better way to accomplish what I want using something other than the UDF?Worksheets("Sheet1").Range("A4:H100").Sort key1:=Worksheets("Sheet1").Range("D4")If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I will try to attach a screenshot of the sheet to make things easier.

Thanks in advance!

Positive cash flow cells all have green background,

Negative cash flow cells, all have red background.

I calculate all the "sub-total" columns into one "total sum" cell.

Based on this "total sum" I want the cell background to be red if the total sum is negative, or green if total sum is positive.

How do I make the cell change to the proper background color based on whether it's a negative or postive total sum?

(i.e. anything from .01 and up would be green backround color, anything from zero or less than zero, would be red background color)

Current IF THEN ELSE nested statement is ::

=IF(D2 < $G$1,$G$1-1,IF(D2 < $H$1,$G$1,IF(D2 < $I$1,$H$1,IF(D2 < $J$1,$I$1,IF(D2 < $K$1,$J$1,IF(D2 < $l$1,$K$1,IF(D2 < $M$1,$L$1,IF(D2 < $N$1,$M$1,D2))))))))

So this is what I'm trying to do. I have dollar amounts in b5 through b256. One dollar amount in each cell. I want to designate a cell in say C260 where I can enter in a random dollar amount and have that dollar amount subtracted from each of the dollar amounts in column b and then total the differences.

I know I can accomplish this by doing something like this =b5-c260+b6-c260+...etc but I'd have to do that 250 some times and that's just silly. Is there a different way to do it.

The goal is to be able to identify a pay rate in c260 which compares itself to the pay rates in column B and then shows how much money we're losing. Ideally I would like to be able to write a formula that only subtracts c260 from numbers that are higher than then number i place in 260 and then add them up. Hmmmmmm? Does that make sense?

First of all, I am new to this forum so I apologize in advance for any inconveniences with this post.

I am trying to find a way to find highlighted cells (which I highlighted using conditional formatting), displaying the entire row, and hide any other rows. Currently, I used conditional formatting on a worksheet called 'Bottleneck Order Report" to find the bottom 5 values in column G called "Total SKUS". I displayed the bottom five cells with the cell color being yellow and the font bold and red. For this worksheet, I only need the 5 rows which include the lowest values in column G to be displayed. Is there a way to hide any rows that doesn't include those 5 highlighted cells?

I have another worksheet called 'Inventory Analysis'. I had the same column "Total SKUS" in column J in this worksheet. Just like in the 'Bottleneck Order Report' I had the five bottom values in the "Total SKUS" column (J in this case) highlighted in cell color yellow, font bold and red, using conditional formatting. I initially wanted a vba code to find the 5 highlighted cells in 'Inventory Analysis' column J, copy certain columns of that row (or the entire row) and paste it to a new worksheet (such as 'Bottleneck Order Report'). However, this seemed too difficult.

Here is the vba code I used on a seperate worksheet that almost does what I need, except instead of finding cells based on cell color, it just finds for less than or greater than 0.

Private Sub Worksheet_Activate() Dim HiddenRow&, RowRange As Range, RowRangeValue& '***************************** '< Set the 1st & last rows to be hidden > Const FirstRow As Long = 4 Const LastRow As Long = 100 '< Set the columns that may contain data > Const FirstCol As String = "B" Const LastCol As String = "G" '***************************** ActiveWindow.DisplayZeros = False Application.ScreenUpdating = False For HiddenRow = FirstRow To LastRow '(we're using columns B to G here) Set RowRange = Range(FirstCol & HiddenRow & _ ":" & LastCol & HiddenRow) 'sums the entries in cells in the RowRange RowRangeValue = Application.Sum(RowRange.Value) If RowRangeValue <> 0 Then 'there's something in this row - don't hide Rows(HiddenRow).EntireRow.Hidden = False Else 'there's nothing in this row yet - hide it Rows(HiddenRow).EntireRow.Hidden = True End If Next HiddenRow Application.ScreenUpdating = True End Sub

Any help would be greatly appreciated. Thank you!

Thanks.

Looking to get some quick help. I am trying to find the sum based on the following spreadsheet.

I would like to find the sum of the delay costs (Column J), where the request arrival time (Column E), is within a specified Time rage.

In this particular case, I would like to find the sum of costs (Column J), where the request arrival time is between TIME(8,0,0) and TIME(16,0,0).

How would I go about doing this?

Thanks! =)

[IMG][/IMG]

I'm writing a macro that I will link to a button on-screen. The macro should run through each cell in a column (Column C, in my example) and change the cell based on what is currently in it. For more clarification, the original data in the cells is should be a number (1000 ; 2000 ; 2050 ; 4090) and after the macro is run the cells should read something like (5 ; 6 ; 7 ) (values are arbitrary). I want the converted data to be placed in a new worksheet so the user can compare and also ensure they entered everything correctly in the original sheet if they need to look back on it.

This doesn't seem like a very complicated task, but I'm a newb and am getting hung up easily. I'm not getting errors, but my code isn't doing much other than copying the sheet. Here's my code:

VB:ChangeValue() Dim DoYouWantToContinue As String Dim c As Range Dim InputAmount As Variant Dim ConvertedAmount As Long 'Safety check DoYouWantToContinue = MsgBox("Are you sure you want to run this? This will permanantly convert all values in Column C." _ , vbYesNo + vbQuestion, "Continue?") If DoYouWantToContinue = vbNo Then Exit Sub Application.ScreenUpdating = False ActiveWorkbook.ActiveSheet.Copy _ Before:=ActiveWorkbook.Sheets("Sheet1") 'Replace "Sheet1" with sheet name . For Each c In Range("C1", Range("C65536").End(xlUp)) Select Case InputAmount Case "": Exit Sub Case 1000 To 2000: ConvertedAmount = 5 Case 2001 To 3000: ConvertedAmount = 7 Case 3001 To 4000: ConvertedAmount = 9 Case 4001 To 5000: ConvertedAmount = 10 Case > 5000: Exit Sub End Select Range("C1") = ConvertedAmount Next c Application.ScreenUpdating = True End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks in advance for any corrections you may be able to provide!

Here I just simplify the data as show in attachment

From attachment i need to sum up the value from oct 2009 till sept 2010,base on the code.

So by using vlookup,what should I select for the 4 condition?

Lookup_value:

Table_array:

Col_index_num:

Range_lookup:

It may have other way to do but I hope to get the answer by using the condition above. Thanks

What I want to do is have column C for each row evaluate to True if either A or B in that row is selected and false if neither is selected for that row. Also, if there is no selection within A1:B100, I would like all cells in C1:C100 to evaluate to True as a default case.

I searched around a little bit in this forum and found macros that will make a one-time change, but not one that will constantly update based on current selections (i.e., if it's possible for this to auto-update instead of having to click an icon or a shortcut key, that would add an extra wow factor to the report). Thanks.

Good day to you all,

Can any one could tell me a simple formula/VBA code to automate the grandtotal in column "H" based on each item code.Even though it is possible to arrive by having a pivot table and also by subtotal, it is highly recommeded to have it in this format only.It is difficut to arrive grand total for nearly 4000 rows , just by summing each item separately.

Can anyone help me to arrive at solution expected?

Thank you

Can someone tell me how to delete cells based on background color?

-Thanks

this formula in column P (cell P13 for this example, but to work thru P61)

=IF(B13="FEMALE",SUM(J13+L13+N13),IF(B13="MALE",SUM(J13-M13-N13)))

this formula in column V (cell V13 for this example, but to work thru V61)

=IF(B13="FEMALE",SUM(J13+M13),IF(B13="MALE",SUM(J13-M13)))

this formula in column Z (cell Z13 for this example, but to work thru Z61)

=IF(B13="FEMALE",SUM(J13-L13),IF(B13="MALE",SUM(J13+L13)))

Thanks very much for any help with this.

E

No luck finding an answer? You could always try Google.