Hi,

I'm looking for a way to make sums based on two criteria which have to match before a sum is made.

I have a list which is linked to and access database which has a number of columns:

Client_id / Sales_Type / Sales_YEAR / Jan / Feb / Mar / ....

The Sales_Type currently consists of "Actual" & "Estimate", Sales_Year consists of 2007 & 2008.

Underneath the worksheet I would like to calculate the total of all the Actual 2007, Actual 2008 and Estimate 2008 for each month.

Somehow I need to be able to tell Excell to SumIf Column B = "Actual" and Column C = "2007" then sum all the data from that month (e.g. Jan) at the bottom.

Is this possible?

I'm looking for a way to make sums based on two criteria which have to match before a sum is made.

I have a list which is linked to and access database which has a number of columns:

Client_id / Sales_Type / Sales_YEAR / Jan / Feb / Mar / ....

The Sales_Type currently consists of "Actual" & "Estimate", Sales_Year consists of 2007 & 2008.

Underneath the worksheet I would like to calculate the total of all the Actual 2007, Actual 2008 and Estimate 2008 for each month.

Somehow I need to be able to tell Excell to SumIf Column B = "Actual" and Column C = "2007" then sum all the data from that month (e.g. Jan) at the bottom.

Is this possible?

- Multi Criteria Sum Formula Very Slow
- Excel: SumIf with Multiple Criteria. Excel Formulas & Functions
- Multi nested IF formula help needed
- Create multi-condition sum formula
- Sum formula with multiple criterias
- HELP:Array Sum Formulas are making my Excel file hang!!
- Question regarding wildcard in multi-criteria IF formula
- Didnt work- Complex Criteria Summing ;(
- Question regarding wildcard in multi-criteria IF formula
- Didnt work- Complex Criteria Summing ;(
- Sum Formula with 3 criteria
- VBA Help - Offset function in sum formula
- VBA Help - FillDown sum formula from activecell in same column
- Check if a cell has a SUM formula in it
- Sum formula in a cell from 2 file
- Copy and paste rows with sum formulas
- Multi-result Array Formula With Max
- VBA Sum Formula
- Modified VBA Sum formula
- VBA Sum Formula be modified?
- Sum Formula In Vba Adjusts To List Length
- Sum Formula With A Variable In Vba
- Add Row In Sum Formula
- Offset SUM Formula

I have previously been performing a multi level SUMIF of named ranges with a large data range.

This works but its inefficient as it would be better to preselect a subset range before the SUMIF begins matching criteria.

Basically, i'd like to select a range of rows which will parse into the SUMIF formula.

I have solved this by referencing an external cell to create the range.

Thanks in advance

Joel PJ

Excel: Sum If with Multiple Criteria. How to Sum cells that meet multiple criteria. Excel Formulas & Functions

column I contains a person’s age as of that date,

column L contains their salary,

column M contains 4% of column L (invested on year 1), and

columns N through AP represent years 2 through 30, and equal the future value of the original investment at 4% interest, plus another 4% investment. Row 7 has the numbers 2 through 30 on each of the columns N through AP.

The number of employees may vary from list to list, however each list starts on row 8, and goes from the lowest age up to the highest age. This particular list has 141 employees, (manually filled in on cell A3). Row 8 age is 26 and row 148 is 65. The average age (cell I3) is 47.

Here’s my dilemma. I had initially assumed that once a person reached 65, they would continue to accrue interest but wouldn’t have any more investments. So, (using row 71 as an example where the person is 47), my formulas are as follows:

I =FLOOR(DAYS360(G71,DATE(2007,12,1))/360,1)

L = 95,966

M =L71*0.04

N =IF($I71+N$7>65,M71*4%+M71,FV(4%,N$7,-$M71))

O =IF($I71+O$7>65,N71*4%+N71,FV(4%,O$7,-$M71))

And so on

Underneath the final row is a total of each column (year).

However, I now need to assume that each year that:

1. As long as a person is working, they will continue to receive 4% investment plus interest. That part is simple, but here is where it gets complicated.

2. Each year, starting at year 2, 2% (rounded to the nearest whole person – reflected as formula =ROUND(A3*2%,0) in cell B3) of the total number of employees of average age terminate employment. They are then replaced by the same number of people at same age, same salary, but starting at the initial investment year 1.

Issue 1: what if there are no people of that exact age? Or what if there are more than 2% of people of that age?

3. In my scenario, there are 4 people age 46 in year 1, so with an average age of 47, on year 2, 3 people (2% of 141) age 47 would terminate, so the cells N67, 68 and 69 would need to reflect year 1 (column M) totals, and each year (columns O-AP) after that build upon those amounts.

4. 2% (also rounded) of the oldest age people retire. They are replaced by the same number of youngest people on the list, also starting at year 1. Since I know that the employees start on row 8 and cell A3 tells me the number of employees, that should help.

I first thought I could change the totals to a formula instead of simply a sum, (and not knowing how many employees might be in each list keep the totals on row 2 instead of a row underneath the list.

However, regardless of which row the totals are in, the formulas in cells N through AP definitely need to be changed to eliminate the current “if true, then they only get interest” portion. That part is easy, but they also need to take into account if they fall in the bracket where they would be one who would retire or terminate, and then have to start using a different formula back at year one and the subsequent years added to that. Which means that for example, someone who is 55 in year 1 turns 65 in year 10, and the formula in that cell suddenly equals true that they retire, and change to year 1 investment of age 26. Then their year 11 gets 4% added to that plus interest… Yikes.

So I am pretty sure those cells would require quite the multi-nested IF formula, but how I would go about doing it is over my head. Any help would be greatly appreciated, if I didn’t give you a headache already reading this!

Date, Rain, Snow

The worksheet also contains a "period start" entry and a "period end" entry.

The "date" column contains every date for the year. For each day of the month, I am recording the appropriate amount of precipitation (and when there is none, entering a zero). I am trying to create a formula that will calculate "Month to Date" information. So, for example, what is October's precipitation to date since that is the period in which we currently are.

Here's one of the things I tried:

SUM(IF(TEST1)*(TEST2)...) where "TEST1" was DateColumn >= period start and "TEST2" was DateColumn

Thank you,

I am using array sum formulas to calculate based on 3-4 criteria. My input range is about 200 X 4000 (R XC) size and I have roughly 1500 cells in my output sheet trying to calculate using array sums on this range.

The minute I hit F9 ( if i am on manual calc mode) the file is hanging/freezing. Does anyone have any solution? Pleassse help!!

spellings into the TRUE/FALSE criteria check. Column AG contains many

various companies, which then need to be sorted into two classes (N or

D).

My question is regarding the wildcard, which doesn't seem to be working

properly. If column AG contains: BestBuy, Best Buy, and Best Buy Co...

the formula is counting each of them as a FALSE statement because the

formula is only pulling the exact phrase of "Best" instead of "Best

(*everything after it)".

This is my current Excel 2003 formula:

=IF(OR(AG5="Good*",AG5="Best*",AG5="Circuit*"),"N" ,"D")

What do I need to change so that the formula will pull in the wildcard

results of Best_Buy_, Best _Buy_, and Best__Buy_Co_ and specify them as

a TRUE statement and list "N" as the final result?

--

Malvaro

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

Malvaro's Profile: http://www.excelforum.com/member.php...o&userid=29589

View this thread: http://www.excelforum.com/showthread...hreadid=492904

Having a problem in excel which is driving me mad! I have a list of

data and i want to create a conditional sumif formula where the sum is

taken only of the last three entries. For our league analysis i look at

a team results history for the last three games by adding up the goals

for and against for last three matches. I want to write a formula which

would look through the list and automatically add the goals for the last

three entries for a team. This cant be done with a traditional sumif

formula as the range is specified and this may include the last four or

five games for a team whilst three for another.

Greatly appreciated,

Karl

complex criteria summing

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

Karl,

I did not look at your attachment, but if you have a list of dates,

then you can use a formula like

this...

Array enter (enter using Ctrl-Shift-Enter)

=SUM(IF(RANK(A2:A15,A2:A15)

My question is regarding the wildcard, which doesn't seem to be working properly. If column AG contains: BestBuy, Best Buy, and Best Buy Co... the formula is counting each of them as a FALSE statement because the formula is only pulling the exact phrase of "Best" instead of "Best (*everything after it)".

This is my current Excel 2003 formula:

=IF(OR(AG5="Good*",AG5="Best*",AG5="Circuit*"),"N","D")

What do I need to change so that the formula will pull in the wildcard results of BestBuy, Best Buy, and Best Buy Co and specify them as a TRUE statement and list "N" as the final result?

Having a problem in excel which is driving me mad! I have a list of data and i want to create a conditional sumif formula where the sum is taken only of the last three entries. For our league analysis i look at a team results history for the last three games by adding up the goals for and against for last three matches. I want to write a formula which would look through the list and automatically add the goals for the last three entries for a team. This cant be done with a traditional sumif formula as the range is specified and this may include the last four or five games for a team whilst three for another.

Greatly appreciated,

Karl

Re: complex criteria summing

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

Karl,

I did not look at your attachment, but if you have a list of dates, then you can use a formula like

this...

Array enter (enter using Ctrl-Shift-Enter)

=SUM(IF(RANK(A2:A15,A2:A15)<4,B2:B15,0))

Where dates are in column A, and the numbers to be summed are in column B.

HTH,

Bernie

MS Excel MVP

thanks for your reply, that would not work however as the criteria is not date driven but is looking for the last three entries of the event in two columns, i have attached the spreadsheet along with an example of the current formula im using which is fixed in range so would return incorrect values if more or less than my target number of events would occur, thanks

I have morning shift and night shift. I want to make a formula that adds up the number of people working per shift. I need my sum formula to add to the total count for morning shift if the person's opening time cell contains a 11:00 AM, OPEN, or 11:30 AM. I also need one for the closing shift to do the same for the leaving time if the time says ??, or CLOSE.

I know it will require a field in the formula.. But if i knew the if>then part then i could figure out the range..

Parent Name (H1) q1(i1) q2 (j1) q3 (k1) qtd (l1)

The Active Cell pointer is @ cell L2 and needs to have a sum formula which should calculate the sum from i1 through activecell -1 column i.e. k1. This is my current formula but its not working. It just pastes the sum formula in excel but does not do any calculation and I am not sure how to fill it down.

ActiveCell.Formula = "=SUM(ActiveCell.Offset(0, -1):H2)"

ActiveCell.Select

Selection.FillDown

Thanks for your help on this.

ActiveCell.Formula = "=SUM(" & ActiveCell.Offset(0, -1).Address & ":H2)"

Sample of the xls file. The cells are in parenthesis.

Parent Name (H1) q1(i1) q2 (j1) q3 (k1) qtd (l1)

I have a range of numeric data arranged in tables one below the other sparring few rows between them, where i generate this data using the random function.

In a particular column, I run a macro and it enters the random function into it. But the problem is that at the end of the data in the column of a table is a sum function that sums all the values in the usedrange in the column. The macro all fills this sum formula with the random function, which I dont want.

Hence, I need to find a way to check if the cell contains the sum formula. If yes, then don't fill it with random formula.

Any help would be highly appreciated.

I have 3 file. one is consolidate date and other 2 is source.

now i want to link insert sum formula in console in gren colored cells only from soruce 1 & 2 green colored cell through VBA

i.e

in Console excel sheet cell BR.Formula = =+'[Source 2.xls]P&L-GL wise'!$B$6+'[Source 1.xls]P&L-GL wise'!$B$6 and the remaing grean colored cells also

I am trying to copy and paste a row that has sum and counta formulas to multiple rows that have different ranges. When I do this, I am getting the range of the first copied row and I have to go into each row and expand each range.

example: copy rows 1-10 (it has the sum formula)

paste to row 20 (same formula) but only want to caputure rows 18-19, not the last 10 rows.

However I cannot get this to work with some of the functions as they use Max or other array aware functions and so ruturn the same result for each cell they are enetered into.

Example for A1=2,B1=4

=2*A1:B1

array enetered gives 4,8

However

=Max(2*A1:B1,5)

array enetered gives 8,8 rather than 5,8

I have tried creating my own version of a max function, but am having problems getting it to evaluate array functions.

Please help.

I've a workbook in which I run an excel macro to filter out data for five regions W1, W2, W3, W4, W5, the macro creates five sheets (w1, w2, w3, w4, w5) and places them in the same workbook along with the original master sheet called "west".

The data in all the sheets is layed out in an identical fashion except that the the number of rows in each sheet will differ depending on the number of records for each region in the master sheet "west".

I was looking for some vba coding to automatically add sum totals in two columns (I & J) for all the five region worksheets.

The first record always begins from row 4, in all the sheets.

so as an example W1 sheet could have a sum formula in I20 = Sum (I4:I8)

and in COl J as Sum(J4:J18). Row 19 is a blank row, and the intention is to leave a blank row just before placing the sum total in all the sheets.

W2 will have the same starting range but might differ in how many rows to sum .

And so on for the 4 remaining region worksheets in the workbook.

And help will be appreciated. I've been working on putting this extract region macro together for 3 days now and this is the final remaining part.

Thanks,

K

VB:Instead of putting the sum formula I would like the code to put this formula instead.SubTotals() For Each NumRange In Columns("M").SpecialCells(xlConstants, xlNumbers).Areas SumAddr = NumRange.Address(False, False) NumRange.Offset(NumRange.Count, 0).Resize(1, 1).Formula = "=SUM(" & SumAddr & ")" C = NumRange.Count Next NumRange NoData: End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

=SUBTOTAL(VLOOKUP(O3,{"Sum",9;"Average",1;"Count",2;"Min",5;"Max",4},2,0),K2:K37)

Look at my sample worksheet in collumn M it automatically sums in that collumn by using the code above.

Can the code be modified to put this formula in collumn M. The sum code above does'nt have ranges in the code can it do the same thing with this formula below?

=SUBTOTAL(VLOOKUP(O3,{"Sum",9;"Average",1;"Count",2;"Min",5;"Max",4},2,0),K2:K37)

VB:AA_DownTime() Dim r As Range, txt As String For Each r In Range("d2", Range("d65536").End(xlUp)) If r.Offset(, -3) = "Without Pattern Change" Then txt = Left(txt, Len(txt) - 1) r.Formula = "=sum(" & txt & ")": txt = "" ElseIf r.Offset(, 1) "S" And Not r.Offset(, -3) Like "*Pattern*" Then txt = txt & r.Address(0, 0) & "," End If Next End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I tried doing it with a for loop x = a to b; by setting a as the starting row and b as ending and adding all cells(x, column)

y=y+cells(x,column).value

But this would give me my value and I would have to redo the macro everytime I wanted the sum value, the value could also be changed manually instead of being referenced with a sum formula which would preserve truthfulness of the sum.

I would like to put the sum formula into the the code so that it would add from row a to b. I Would also like to be able to look at the formula in the cell in excel view. How can I Manipulate this to get my desired result.

ActiveCell.offset(x,y).FormulaR1C1 = "=SUM(R[-3]C[1]:R[7]C[1])"

How can I take a formula like this or something similar like this and get it to work. Activecell could be replaced with referenced/relative cell.

is something like this possible, or is this on the right track

ActiveCell.offset(x,y).FormulaR1C1 = "=SUM(R[a]C[1]:R[b]C[1])"

I appreciate any help greatly.

I'm trying to enter the =sum formula using vba with one of the ranges in the formula being a variable. Can not seem to get the following to enter the formula correctly.

VB:ThanksLast_cell_4 = Range("A4").End(xlDown).Offset(0, 3).Address Last_cell_5 = Range("A4").End(xlDown).Offset(1, 15).Address Range(Last_cell_4).Offset(1, 0).Formula = "=Sum(D4..Last_cell_5)"If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I want to sum values in A1 to A6, so i use

VB:......i add rows aftere A6 what i shall put in the sum formula to include newly the added rows=Sum(A1:A6)If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I would really appreciate if someone could help me with the offset function.

I have months listed in this format in columns/rows 01/01/06 , 02/01/06 and so on in ( J9 to L9 ).

I have a reference cell A5 = Month in the same format. This value changes every month.

I want to use the SUM Formula of YTD in column/row W9 . If the month is 05/01/6 then the sum should be C9 to G9 and so on.

I would really appreciate if I could have a sligt help her. I dont understand how the Offset function works.

Thanks