Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Sum Cells Based on Current Qtr

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


Post your answer or comment

comments powered by Disqus
Hello All,

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

I use your "CountColor" and "SumColor" functions regualrly and now want to count or sum cells based on BOTH their colour AND their contents.
Has anybody written a function to achieve this?
Many Thanks Finny

Hi - I've tried searching the forum and web for this without success.

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

I am trying to use a function kindly listed by Dave, that allows for a SUM to be done on cells which have a certain color index. I have modified it very slightly, but unable to set the call to it without getting errors, perhaps someone can shed some light on what i am doing wrong.

Dave's original code is

	VB:
	
) 
    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 Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My modified version is

	VB:
	
) 
    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 Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This modified version compiles without error.

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:
	
) 

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

Thanks to all in advance for the help

How to configure 1 cell that it will pick a cell base on current month?
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.

Guys

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.

I'm really new to the whole VBA-thing, so if you could dumb-it-down for me, I'd be appreciative!

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!

I am not a whiz at macros and I can sometimes figure them out, but this one has really got me stumped. I love the function that is explained at http://www.ozgrid.com/VBA/Sum.htm, but I am trying to figure out how to make it do Average instead of Sum. Any help will be greatly appreciated.

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 Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi, I'm trying to calculate Total Amount based on current month.
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

Excel 2003--I have a worksheet that's exported from another program and isn't formatted in a way that allows me to sort and sum.

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!

I have a cell containing a UDF which sums cells in a column based on their fill color. This works fine as new colored cells are added to the column. The problem I have is that when a date is changed in an adjacent column, multiple columns (including the one with the cells being summed) are sorted by dates. After the sort, my cells which contain the UDFs then display #VALUE! Adding another amount to the summed column then causes the UDF to display the correct values again. I have dates that expense reports were filed in column C, the amount that was filed in column E (contains the colored cells), and the date the employees were reimbursed in column D. The cells in column E are colored green, yellow, or red depending on how many days since the reports were filed but not reimbursed with the following vba

	VB:
	
 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 FileDate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Cell C2 contains this formula for the following UDF (C1 has the fill color being evaluated by the UDF)
=SumColor(C1,E4:E100)

	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 Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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.

	VB:
	
 
    Set 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 - DateFiled 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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 C2

	VB:
	
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
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?
I will try to attach a screenshot of the sheet to make things easier.
Thanks in advance!

I have excel sheet with colored cells.

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)

I have a spreadsheet that is used once a week that gathers a lot of information, that is then sorted. I am fairly new to VBA. I need to be able to manipulate the dates listed in Column "D" based on the date that is in Cells G1 - P1. Cell G1 has formula "=Today()" so that it will display the days date, each cell after across the row has formula "=G1+7"; "=H1+7"; etc. so that I have a 6 week range. The last 4 cells only need to have the next months date in them: ex:"09/01/2008"; "10/01/2008"; etc. A nested IF THEN ELSE statement isn't long enough. I can do by recording a macro while entering the formula, but still run into issue. Is there a way to use VB IF ELSEIF that will check value in cell and change based on that.
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))))))))

Summing the difference of multiple cells based on the value of a cell. Does that even make sense.

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?

Hello,

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!

I am looking for a formula that will sum a range of cells based on a certain color. I have conditional formated certain rows, and i would like a formula that sums the rows in the sheet looking for that color.

I am trying to SUM cells in Excel 2008 based on the cell's background color. I know there is a way to create a formula for this if you are using PC versions of Excel, but I can't figure out how to do it for Excel 2008. Do I need to use Applescript? If so, what do I need to do?

Thanks.

Hi guys,
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]

Hi,

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 Sub 

If 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!

Hi,I had a trouble to sum up the value using vlookup.
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

I have a table (cells A1:B100) where A contains data labels and B contains the actual data.
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.

HI,

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

Hello, On Ozgrid I found the thread that explains how to sum/count cells based on background color (http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm)

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

-Thanks

Hello Everyone. I am trying to make my macro insert formulas into specific cells based on certain criteria. Basically, looking for an entry in Column B, Rows 13 thru 61. Column B is setup to only allow one of 2 entries (MALE OR FEMALE) Once the macro sees an entry, it would paste:

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.