Free Microsoft Excel 2013 Quick Reference

Sumif using a multiple column range Results

I just realized after I posted this that if I add another column next to A
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.

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.

I have created a macro that count total rows in sheet 1 and sheet2.

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

Granted, I have found several formulae that are meant to solve this sort of problem, but as much as I fiddle with it to match my context I can't seem to get an elegant solution. Nor any solution, actually.

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.

Hello,

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

Here's another fun one that's sucked up days of my life with no progress.

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

Ive tried combining a dynamic named range using the OFFSET command with variations of SUMIF and SUMPRODUCT formulas and cannot get the concept to work. Ive 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 cant 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 Ive 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!

Oh for Excel 2007 and the SUMIFS function but my IT dept won't upgrade so here I am.

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.

I have a named range with multiple columns:
need
in other words: sum numbers in the range's column A where Column B= cell A1.

Probably simple but...

Thanks

I'm aware of the SUMIF function that sums up a range based on one criteria. But I'm wondering if there's a function that does the same thing but allows multiple criteria in a SINGLE column.

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.

Hi there fellow Excel-ers:

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!

I have multiple tables of data separated by range names. Each Range contains month/day for the full year Jan 01-Dec 31. Each range also has 8 columns of data along with the date range. Is there a way for me to pull just the monthly sum based on a range name for a certain column?

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

Hi,

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/

Hello,

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

Hi,

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

This is my first foray into Ozgrid, so please forgive any inefficiencies I may have in presenting my problem. I’ll be happy to clarify further if needed and/or post sample files on request.

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.

I have a spreadsheet where my source data is a Profit & Loss exported to Excel from an accounting package. Unfortunately the layout of the exported Profit & Loss report is very poor to facilitate data manipulation. I'm unable to change the layout of this report (as the accounting program doesn't give the option and the users of this spreadsheet do not have the time nor skill to reformat the layout of the Excel export into something more useable).

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

I have a worksheet that uses an array formula to sum an amount based on the criteria in multiple columns. I can't use "sumif" because there are multiple columns and I understand that you can only use sumif with one column.

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!

Hi,

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

Hello

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

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


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