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

Free Microsoft Excel 2013 Quick Reference

Multi-Criteria Sum Formula

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?


Post your answer or comment

comments powered by Disqus
Hey everyone,

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: SumIf with Multiple Criteria. Excel Formulas & Functions

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

I have a spreadsheet with a list of employees where
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!

I have the following worksheet columns as input to a daily report:

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

How would I write a sum formula where if column B = "Employee" and column G "Sl", "MT", "HO", "GY" or "CO", then sum column L?
Thank you,

Hi,

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

For whatver reason the wildcard doesn't seem to be pulling alternative
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

Hi all
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)

For whatver reason the wildcard doesn't seem to be pulling alternative 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 BestBuy, Best Buy, and Best Buy Co and specify them as a TRUE statement and list "N" as the final result?

Hi all
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

Ok.. So I'm working on a schedule for work and I needed some help with a formula.

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..

I have the code written to find the ActiveCell which is the first blank cell after the last column of data. However now within that cell I want to enter a formula which calculates a 'Total' which should sum up the ActiveCell -1 (same row but previous column) through cell i1 and then fill the formula down. This is a sample of how the xls looks like. The cells are in parenthesis :
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.

I have the Formula correctly in the ActiveCell to calculate sum of the 3 columns to its left. Now I need to write the code to fill it down in the active cell column to calculate the sum and then copy the entire range of columns from cells H1:L1 (Active Cell column) to sheet 2. Please help with what the code should be. Below is what I have for the sum formula in the active cell.

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)

Dear friends,

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.

Hi
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

Hi:

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.

I am using multi-result array formulas to reduce error checking when processing arrays of numbers.
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.

HI,

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

The code below works great its a sum formula


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

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Instead of putting the sum formula I would like the code to put this formula instead.
=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)

I got this code almost to work But look at the sample worksheet in collumn D11 the value should be 0.83. It should not sum the cells above and it uses a regular =SUM formula. Is there somehow to modified the formula code to select the certain cells?


	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 Sub 

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


I want to put in a sum formula into my vba code. The length of the row range to sum may change and the starting cell and ending cell may both change. The column though will always be the same.

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.

Know this easy, but can't seem to see the answer today.

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:
	
 
Last_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
Thanks

Hi There

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

	VB:
	
=Sum(A1:A6) 

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

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


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