Free Microsoft Excel 2013 Quick Reference

- SUMIF using a multiple-column range
- SUMIF using a multiple-column range
- Use of Variable in Range In Excel Macro
- SUMIFS, multiple criteria incl. unique values
- Sumif with multiple critera range and sum_range
- Sum A Named Range Across Multiple Columns
- SUMIF multiple criteria (Excel 2003 - Windows XP)
- Sumifs using large named range
- Is there a function that uses multiple criteria in a single column?
- Sumif based on multiple criteria
- SUMIF of a RANGE NAME BASED ON MONTH
- Conditional Range Formula
- Sum With 2 Criteria Across Multiple Sheets
- Sumproduct, sumif with range of criteria and multiple column sum
- Sum On Multiple Critera Across Multiple Sheets
- SUMIF on Badly Laid out Data
- Using Named Ranges in Array formulas?
- Problem with SumIF()
- Multiple criterion SumIf in VBA
- SUMIF and the Multiple Sheet Conundrum

with the same values and do SUMIF(A1:B3,"=1",C13) then I get my intended

result, but is there a way to do this without adding that extra redundant

column? Or is Excel just not smart enough to tell that when you only give it

one column for your criteria to use that for all sum columns. Oh well...

"Stefan Wrobel" wrote:

> I have a very simple task and I don't get why SUMIF isn't doing what I expect

> it to. Observe this set of data:

>

> 1 15 4

> 0 3 2

> 1 1 8

>

> If I do SUMIF(A1:A3,"=1",B1:C3) then I just get 16, which is the result of

> SUMIF(A1:A3,"=1",B1:C3), rather than 28, which is the sum of 15+4+1+8. I

> realize that I could do SUMIF(A1:A3,"=1",B1:B3) + SUMIF(A1:A3,"=1",C1:C3),

> but that just seems silly, since SUMIF takes in a Range, I don't get why it

> doesn't actually sum over the range. And yes I have tried using

> Ctrl+Shift+Enter when entering it, no help.

it to. Observe this set of data:

1 15 4

0 3 2

1 1 8

If I do SUMIF(A1:A3,"=1",B1:C3) then I just get 16, which is the result of

SUMIF(A1:A3,"=1",B1:C3), rather than 28, which is the sum of 15+4+1+8. I

realize that I could do SUMIF(A1:A3,"=1",B1:B3) + SUMIF(A1:A3,"=1",C1:C3),

but that just seems silly, since SUMIF takes in a Range, I don't get why it

doesn't actually sum over the range. And yes I have tried using

Ctrl+Shift+Enter when entering it, no help.

In Sheet one there is data with name in multiple columns and i want the sum in sheet 2 with single instance from sheet1 (as if sheet 1 have 3 instance of a so it will add all these values and will show in sheet 2 in front of a).

I have done it so far by using sumif function but the problem is the range i am using right now is static like sheet1!B1 :Sheet1!B17 and i want the end row value to be dynamic as my rows can grow in future but the column will remain constant.

Attaching the file with the mail , i need to do sum in sheet 2 using variable row number.

Thanks in advance

Consider five columns:

Name # Type Value Activation James 0 type1 200 1 James 1 type3 2 0 James 2 type1 400 0 James 3 type2 40 1 John 0 type1 200 1 John 4 type3 4 1 John 2 type1 400 1 Caitlin 3 type2 40 1 Caitlin 5 type1 200 1

The "Name" column isn't really relevant, but it illustrates why I don't have unique values for column "#". Of the values in the "Value" column, I want to sum those that meet the following criteria:

1. "Type" = type1

2. "Activation" = 1

3. The value in "#" is unique (i.e. no double addition).

So only the second, eighth and tenth rows satisfy the above, returning a sum of 800. Obviously, I'd like the formula to work for a range of values, specifically {type1, type2, ... , typen} for "Type" and {0,1} for "Activation." As long as values with the same "#" aren't summed twice then I'm happy. Until I ran into the double-addition issue I was using this formula, seemingly successfully:

Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Â Z100Â =SUMIFS(Value,Type,"type1",Activation,1)Â

I've posted this same question here and here (a potential solution using a helper column was posted, but a solution that doesn't require a helper column is preferable). Help would be appreciated.

I have a worksheet where I'm trying to use the sumif function to sum over

multiple criteria ranges and multiple sum_ranges. The following formula,

which shows three ranges, (surprisingly) works. Is there a more compact way

to implement this? This is for a budget spreadsheet where each of the ranges

(J31:J36 for example, are one month. So the full scale formula would have 12

ranges and sum_ranges. Notice that the sum_ranges are offset two columns

right of the critera range.

=SUMIF(($J$31:$J$36:$O$31:$O$36:$T$31:$T$36),"="&C 47,($L$31:$L$36:$Q$31:$Q$36:$V$31:$V$36))

If I rearranged the columns, would it that make it possible for a more

compact formula to work?

Thanks,

John

I have groups of columns representing the days of the week where numbers representing amount of work done (just simple whole number quantities) go. I have every other column named as Budget for the budget of work to be done, and every other column beside those named Actual, for actual work done.

So basically on this worksheet, columns A, C, E, G, I, K, etc... all represent the named range Budget (=Sheet2!$A:$A,Sheet2!$C:$C,Sheet2!$E:$E, etc. …), and columns B, D, F, H, J represent the range Actual (=Sheet2!$B:$B,Sheet2!$D:$D,Sheet2!$F:$F, etc. …). I want a separate cell located elsewhere to sum up all the quantities in the Actual columns, and another cell to separately sum up all the quantities for the Budget columns. The trick to this, though, is I need only the quantities summed up to be for the specific row of that sum calculation.

That might not make the most sense so I’ll break it down again. I need to sum all the quantities only in the columns labeled Actual for each single row. It probably needs to a dynamic range/reference that continues expanding to the right as long as there is a number value, since the length of the sum calculations (i.e., number of days involved) is open-ended.

I’ve tried combining a dynamic named range using the OFFSET command with variations of SUMIF and SUMPRODUCT formulas and cannot get the concept to work. I’ve also tried using a standard formula to sum up every other column [ SUMPRODUCT(MOD(COLUMN(1:1),2)*1:1) ], which is more what I need since the range of columns is indefinite, but the problem there is I can’t get that formula to start calculating from the middle of the sheet. It starts at the first column, summing them all, and in actuality I need to start this formula around Column O or P, because of preceding data and calculations. And the SUMIF formula attempts I’ve made always sum up everything in the entire column and I need to keep it contained to values just along the same row.

Any suggestions and assistance are always greatly appreciated! Thanks in advance to all helping out!

I have a spreadsheet where I need to add up some data based on two different criteria.

The first criteria is a drop down list of months in a consolidating worksheet to produce a report and the second is a text string that is fixed. The data is in a different worksheet.

e.g.

I want to total up the sales for January for the London sales team. My data worksheet has 3 columns - Month, Team and Sales.

Month Team Sales

Jan London £5,000

Feb London £4,000

Feb Birmingham £3,000

My consolidating report worksheet has a line for all the sales teams e.g. London, Manchester and Birmingham like so:

Month: <drop down box>

TEAM Total Sales

London (what formula would work here?)

Birmingham

Manchester

The data spreadsheet will be updated each month so the idea is for people to be able to update the data worksheet easily and read a report by month. I wanted to therefore use a whole column e.g C:C rather than specifying a more discrete range of cells.

Formats for the date in both worksheets are the same (and month reverts to the first day of the month for simplicity).

Hope that makes sense. I've tried using an array formula but can't seem to get it to work. Does anyone have any idea where I'm going wrong?

Thanks in advance for any advice that you can give.

need

in other words: sum numbers in the range's column A where Column B= cell A1.

Probably simple but...

Thanks

The SUMIFS function only applies if you have multiple criteria spread across multiple columns *not* in the same single column.

So, any ideas on this? Thanks in advance.

I ran into a roadbloack with a recent exercise and can't figure out a solution:

Basically, I'm having trouble figuring out a way to sum the sales for certain customers based on a certain date range....which is 1 yr before AND 1 yr after ptoduct installation.

I need the sales total for the time periods to go in columns F & G of the "Sales Summary" tab. The "Sales History" tab has all of the sales transactions for each customer but again I only need the sales for a given time frame.

I figured I could use some sort of sumif, index, countif, or maybe even hlookup but to no avail. I'm theorizing I need some combination of these but just can't figure this puzzle out.

Any help would be appreciated....thx!

eg:

RANGE_ONE

01/01/2010 25 20 10

01/02/2010 25 20 10

01/03/2010 25 20 10

01/04/2010 25 20 10

01/05/2010 25 20 10

01/06/2010 25 20 10

01/07/2010 25 20 10

On Sheet2! I would like to maybe have a cell where I type in "1" for January, "2"

for February, etc. and the sum of the month would be pulled in for column 2 or 3 or 4 or whichever column I ask to total.

I use this on a different sheet for something else, so I'm wondering if this can be modified to use RANGE NAMES with a vlookup to specify the column or something.

=SUMPRODUCT(--(MONTH($A$4:$A$368)=$B$3),--(C$4:C$368))

Let me know if I need to include a sample sheet.

Thanks for your help.

David

Not sure how to describe it but I'm trying to take predetermined pounds & multiply them against a rate. That rate is based on the state & range of the pound itself.

For example in the pictures, the green highlighted area is where I want the results to be. The orange highlights are the pounds. The blue higlights are the table I want the pounds to be referenced against. So for the 1/10/11 date in Atlanta, GA, the result should be (956 X $14.88)= $14,225.28

I know the sumif doesnt work with tables across multiple columns. Do I need to change my table? Is there a formula that will work here? I want the formula to be able to determine what rate based on the range of pounds & state & multiply that out using the supplied table.

http://imageshack.us/photo/my-images/577/excelx.jpg/

My question is similar to the thread Count/sum with two criteria from multiple sheets. For example, I would like to use SUM Formula the number of widgets sold by store (Column A) by month (Row 5) for each salesperson (sheets Homer, Marge, and Lisa). I'm using the following SUMPRODUCT equation to achieve the desired results:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$3&"'!A:A"),$A6,INDIRECT("'"&$A$1:$A$3&"'!b:b")))

where my worksheet names are in range A1:A3, the store names are in range A6:A10, and the months are in B5:D5. However, for each month, I have to manually change the column ie. from "'!b:b" to "'!c:c", "'!d:d", etc. In my real worksheet, the columns extend down to ED so to manually change the range would be extremely time consuming. Does anyone know how to do this?

Thanks for your time and happy holidays!

Tesa

I have been working on a problem in excel 2003 but searching the net didn't provide me with and answer.

I have the following structure on sheet1:

A - B - C - ... - X

1 Name - amount1 - amount2 - .... - amountx

2 Bill - 5 - 4 - .... - 10

3 Jill - 4 - 5 - .... - 9

4 Joe - 3 - 6 - .... - 8

5 John - 2 - 7 - .... - 7

6 Mary - 1 - 8 - .... - 6

on sheet2 I have in range Q1-Q2 the names Jill and John

could someone help me construct a formula to calculate the sum of amount1 and amount2

later this formula should extend to amountx (but always starting from amount1 and adding all column up to a certain x)

I know how to create it for only amount1:

=SUMPRODUCT(SUMIF(Sheet1!A2:A6,Q1:Q2,Sheet1!B2:B6)) which gives me 6.

how can I extend this to include amount2 to give the result 18.

I would like be able to express the colums to be summed (amount1...x) using the indirect() formula as the range to be summed is not standard. also the criteria range wll vary over time but that does not seem to be a problem

Thank you all for any help

Iâ€™m relatively new at using the INDIRECT function, and am having a hard time setting up the syntax for ranges, and even knowing if those ranges will work.

I have a workbook with multiple sheets (letâ€™s call them Program sheets) created from a template that contains variable numeric data that I need to sum by creating a formula on a Summary sheet within the same workbook. The criteria for IDing and summing the data from the Program sheets is spread over 3 cells in adjacent columns (letâ€™s call them $E7, $F7 and $J7) on the Program sheets. A string concatenation of these cells will not create a unique string value on any one sheet as there are potentially multiple rows of data on each sheet and across sheets that could have the same value string. The Summary sheet is a report that contains hard-coded values in adjacent cells ($C4, $D4 and $E4) that will match values found in columns E, F and J from the Program sheets.

Iâ€™d like to have the formula sum all values within the range P7:AA70 across all the Program sheets when the entries into E, F and J cells (from Program sheets) match $C4, $D4, and $E4 cells on the Summary sheet, keeping in mind that there could be multiple instances of the same values over several rows within the Program sheets (thatâ€™s OK, because I want each instance to be part of the sum)

Hereâ€™s a formula I created for summing values found in a range based on a single matching criteria across sheets. Can this be adapted to the new sum formula I need?

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheet_List&"'!G7:G70"),'By-Month Summary'!$G7,INDIRECT("'"&Sheet_List&"'!P7:P70")))

Sheet_List is a named range on a separate tab that lists the names of the Program sheets that I need to sum from.

P7:P70 is the range that the sumable data lies in.

G7:G70 is the range that contains values that need to match the criteria on the By-Month Summary sheet cell G7.

For the new formula, I no longer want to sum based on criteria in the G column, but rather on criteria in the multiple columns I outlined in my diatribe above.

An inconvenient but manageable problem is the account number is merged into the same cell as the account description (which makes account number lookups awkward). The main problem, however, is the monthly amount columns for the year are not adjacent to each other - instead they alternate with the comparative monthly column from the prior year (ie July This Year; July Last Year; August This Year; August Last Year, September This Year etc).

I need to be able to do the equivalent of a SUMIF across multiple columns so that I can add the balances for a specified number of months for those account numbers that fall within a certain range (eg sum all accounts between codes 200 and 212 inclusive for months October to December inclusive for the current year).

If the monthly columns for the year were adjacent to each other I could use an array formula, however, I can't see how to do this with any combination of Excel's native formulas. My only solution has been to design a custom function (UDF) but it has slowed the spreadsheet down a fair bit.

I've attached a cut-down sample version for anybody who might have some alternatives to using a UDF. Failing that, any suggestions on how to improve my UDF would be greatly appreciated.

My UDF code is below:

VB:Dim Code As Range Dim MonthColumns As Integer Dim RowCount As Integer Dim CalcResult As Double CalcResult = 0 RowCount = 0 'Lookup code and sum the corresponding month(s) amount if the code is between the code range For Each Code In Range(Database) RowCount = RowCount + 1 On Error Goto SkipCode If Code >= FromCode And Code

For simplicity let's say the formula is this:

{=SUM(($D$2:$D$200=$A$1)*($Q$2:$Q$200=$B$1)*G$2:G$200)}

However with arrays each component's range must be identicle, right? But what if that range will change a lot, I don't want to have to keep changing it. I thought I would therefore use a named ranges which uses the wonderful "=OFFSET($A$1,0,0,COUNTA($A:$A),1)" as the range. So basically:

{=SUM((REP=$A$1)*(LOC=$B$1)*LIC)}

where REP, LOC, and LIC each correspond to columns D, Q, and G, respectively.

However it doesn't work.

Does anyone know if this is possible, or if I'm just doing something wrong?

Thanks!

I am using sumif() for quite long time, all the time I am creating range of different column with merging i.e. (A1&D1, A2&D2.......A10000&D10000) . is there any other way to creat one time and it in case if I am inserting any row then it will not creat any problem in calculation.

Please help me it will save a lot of time for me and give accurate result for me. The sheets I am using is being used by multiple members in the team, and I am finding lot of problem.

Thanks

Krish

I am trying to do a SumIf based on 2 criterion in VBA:

Code:

I would use a SUMPRODUCT if using non-VBA method, equivalent would be:

=SUMPRODUCT(--(A1:A100=6100100),--(B1:B100="P700165",C1:C100)

I have tried using Union and Evaluate to join the criterion ranges PcCol and AcCol but to no avail. This is embedded in a loop which could have up to 50000 iterations so I think I would like to avoid using .Formula="=SUMPRODUCT(etc)" method.

Does anybody have a good idea about how to achieve equivalent multiple criterion SumIf in VBA?

For reference, PcCol, AcCol and pVal and column ranges returned thru Inputbox Type:=8.

Many thanks

Jon

I am admittedly a newbie and have only had a casual acquaintance with Excel up until now. I have a two part question.

I have a spreadsheet that I am using as a bill of material, first sheet is a summarized listing of all the parts used in the product. I want this summarized list to be populated from subsequent sheets that are individual parts lists.

On the summarized sheet, column B has the part numbers and column D is the quantity.

On the individual parts lists, column B and D are the same as the Summarized.

Parts may be used multiple times on multiple parts lists.

Parts are not always listed in the same order and may not occupy the same cell.

I currently have this in the quantity column of my summarized sheet to search the subsequent sheets for individual part quantities.

=SUMIF(B:B,B3,'11088'!D3:D37)+SUMIF(B:B,B3,'11089'!D3:D37)+SUMIF(B:B,B3,'11090'!D3:D37) â€“ etc.

Is this the best way to accomplish this task?

I currently have about 20 individual parts list sheets and this may actually grow. Is there a way to make a named range for the sheets so I can search all of the sheets easily? (a named range that includes all the sheet names). Would that be a 3-D reference? How exactly is this done?

I think I saw it referenced here:

]http://www.mrexcel.com/forum/showthr...ighlight=sumif

Please feel free to speak in newbie speak. I'm a CAD guy, I could tell you how to design something but this stuff is killing me.

Thanks