Free Microsoft Excel 2013 Quick Reference

- SUM function for alternate columns/rows
- Re: Problem with Excel SUM function
- Summing Alternate Column Entries in a Row
- Increment by row when copying across columns (alternative me
- Calculating Mean and Variance for Bivariate Prob. Dist.
- Conditional summing with large amounts of data
- SUMPRODUCT of columns based on matrix position
- Conditional summing with large amounts of data
- SUMPRODUCT Alternative for Large Range
- Sum Across Worksheets To Include New Sheets
- New Excel function SUMABOVE
- Sum YTD Budget
- New Excel function SUMABOVE
- Vba help with sum visible columns
- Sum YTD Budget
- Refer to PivotTable by name in GetPivotData ?
- DSUM with columns as records and rows as values
- VBA: Working with "Areas" within 2d Arrays
- Help with indirect and sumproduct or alternative method (without VBA)
- Exceeded Nested IF Suppressing #DIV/0!

the data is as below..

1st Query 1st Revert 2nd Query 2nd Revert

11-Sep-07 12-Sep-07 13-Sep-07 15-Sep-07

I want the no of times there is data in the columns mentioning (query).

THANKYOU!! Works Great!

JoeD

"Bernie Deitrick" wrote:

> Joe,

>

> Array enter (enter using Ctrl-Shift-Enter)

>

> =SUM(IF(A1:A9<>"",VALUE(RIGHT(A1:A9,LEN(A1:A9)-1)),0))

>

> Of course, change all 3 of the A1:A9 references to your actual range.

>

> HTH,

> Bernie

> MS Excel MVP

>

>

> "JoeD" <JoeD@discussions.microsoft.com> wrote in message

> news:64CC451F-B38C-4612-B232-18517C3AD7DC@microsoft.com...

> > Bernie,

> >

> > Thanks for the suggestion -- I used the

> >

> > =SUMPRODUCT(VALUE(RIGHT(A1:A9,LEN(A1:A9)-1))) formula and it worked fine

> > until it hit a blank cell. I then received the #VALUE# error. Any

> > suggestions on how to get past blank cells? The range of data is around

> > 155

> > rows within the column with several blank cells intermixed within the

> > column.

> >

> > Thanks in advance!

> >

> > Joe

> >

> >

> > "Bernie Deitrick" wrote:

> >

> >> JoeD,

> >>

> >> Try:

> >>

> >> =SUMPRODUCT(VALUE(TRIM(A1:A9)))

> >>

> >> That may or may not work depending on what the actual lead ASCII

> >> character

> >> is.

> >>

> >> Alternatively, you could use

> >>

> >> =SUMPRODUCT(VALUE(RIGHT(A1:A9,LEN(A1:A9)-1)))

> >>

> >> which should work no matter what the lead character is.

> >>

> >> HTH,

> >> Bernie

> >> MS Excel MVP

> >>

> >>

> >> "JoeD" <JoeD@discussions.microsoft.com> wrote in message

> >> news:0F5AAB38-F541-4247-B137-B5FC8272EFB4@microsoft.com...

> >> > I have imported data from a web query into an Excel worksheet. I am

> >> trying

> >> > to add the totals of a column (SUM). My formula in cell A10 is:

> >> =SUM(A1:A9).

> >> > The result displayed in A10 is 0.

> >> >

> >> > When I select cell A1 it's data is displayed in the formula bar, with

> >> > an

> >> > additional space on the left side of the data. If I remove the space

> >> (delete

> >> > key), the SUM function will add just that cell. If I remove the

> >> > 'space'

> >> from

> >> > in from of each entry in the column, the SUM function works fine.

> >> >

> >> > I've tried the tools, options,calculation, automatic settings, but

> >> > that

> >> > does not help. I've tried to "indent" or shift the entire column over,

> >> but

> >> > neither worked.

> >> >

> >> > Anyone have a suggestion?

> >> >

> >> > Thanks for your time.

> >>

> >>

> >>

>

>

>

I am seeking an INBUILT combination of functions (meaning NO programming thru' a macro or user-defined function!!) to find the sum of alternate values in a row.

For example, imagine I have numerical entries in a row, from A5 to U5, and in cell V5 I need to sum just alternate or 1-in-3 entries. That is, for the alternate case, just sum [A5,C5,E5,...], or in the case of 1-in-3, just sum [A5,D5,G5,...]. What formula in cell V5 will help me do this?

Obviously a simple: =SUM(A5:U5) won't do here. Is there something like: =SUM(A5:U5, 2) or =SUM(A5:U5, 3) which tells Excel to sum from A5:U5 BUT in steps of 2 or 3?

Please help... thanks in advance. Again, please note that I cannot afford a macro or some user-defined function to accomplish this.

Thanks.

- Om

Use the OFFSET function to achieve similar results by placing this formula in column D and copying across (as needed):

=SUM(OFFSET($A$1,0,0,COLUMN()-2,1))

The effect is such that at each column to the right of the previous one, the row number increases by 1, and just like the thread mentioned above, will achieve the same results as if you had entered the following:

=SUM($A$1:$A$2) at Column D

=SUM($A$1:$A$3) at Column E

=SUM($A$1:$A$4) at Column F, et cetera.

The values returned are the same as those using the INDIRECT/ADDRESS formula, but this OFFSET function formula is a little shorter (and probably a little faster when dealing with hundreds of formulas, although I haven't tested this).

Regards,

Tom

I trying to find (or alternatively write) functions that calculate mean and variance for bivariate probability distributions.

For example:

Skis

Boots 0 1 2

0 0.08 0.14 0.12

1 0.09 0.17 0.13

2 0.05 0.18 0.04

I wrote my own function for a single variable probability distribution, but this is more complicated since you need to sum rows or columns depending on the variable you are calculating for.

If I have to write this, I guess my first question would be how can I sum up either a row or column from a range and store the solution within an array?

i.e For column(i)

Sum all of the row values

Store in array(i)

Next column

Any suggestions on how to do this, where I can find something that already exists, or how to best approach this in general? The help of this forum is, as always, greatly apreciated.

-Zack

I have a workbook contains 3 sheets

"2002": The first is data for 2002. There are about 35,000 rows of data.

Each row has 5 identifying features (ie columns) plus two of actual numbers.

"1995": The second sheet is similar data for 1995. However there are only

about 8,000 records and only 4 identifying factors.

(The identifying factors are pre-defined lists, with between 8 and 33

options available.)

The third sheet is a summary sheet. What I want to do is dynamically return

a 2D table that summarises any combination of that data (including

aggregating and filtering). I have successfully implemented this using the

following formula:

=SUM((INDIRECT($D$6&"!"&$B$3&"2:"&$B$3&"35393")=$D 18)*

(INDIRECT($D$6&"!"&$B$2&"2:"&$B$2&"35393")=F$16)*

IF(ISBLANK($F$4),1,(INDIRECT($D$6&"!"&$B$4&"2:"&$B $4&"35393")=$F$4))*

IF(ISBLANK($F$5),1,(INDIRECT($D$6&"!"&$B$5&"2:"&$B $5&"35393")=$F$5))*

(INDIRECT($D$6&"!J2:J35393")))

D6 contains either 2002 or 1995 (ie the sheet names).

B2:B5 contains the letters of the columns which are being filtered and/or

displayed (dynamically retrieved by simple formulae)

F4:F5 contain possible filters

This formula is copied across a 33 by 33 range (large enough to cover all

possible combinations of rows/columns)

Unfortunately this calculates very slowly - it currently takes about

30s-1min to calculate on a Intel P4 3GHz, with 500Mb Ram, and tends to

recalculate a lot as you need to change other options to set up the table how

you want it.

I have read a few sites on optimising speed of calculations etc, and they

tend to suggest using alternatives to the sum() function but I am not sure

how they apply here given the versatility I am hoping for.

Any advice or suggestions would be very much apprecaited

Regards

Revontulet

I have created the following attachment to illustrate my problem. I have a square matrix -- say a 10x10 matrix (with data in B2:K11, and column headings on row 1, row headings on column A). I would like to get the sum of products of two columns in the result section below -- cells B14:K23, based on the position of the formula.

For example, cell B14 is the 1,1 item of the matrix so I would like to get the sumproduct of B2:B11 and B2:B11 (or SUMSQ(B2:B11) in this case); alternatively cell I16 is element 8,3 in the matrix so I need to get sumproduct of I4:I11 and D4:D11.

I tried the following formula using the OFFSET function (for cell B14 which is then copy/pasted throughout B14:K23)

But I keep getting a #VALUE! error.

What really drives me crazy is that if I do something like:

, that works perfectly fine (only for the diagonals) but I'm still at a loss for off-diagonal elements.

Any help will be greatly appreciated. Thanks!

I have a workbook contains 3 sheets

"2002": The first is data for 2002. There are about 35,000 rows of data.

Each row has 5 identifying features (ie columns) plus two of actual numbers.

"1995": The second sheet is similar data for 1995. However there are only

about 8,000 records and only 4 identifying factors.

(The identifying factors are pre-defined lists, with between 8 and 33

options available.)

The third sheet is a summary sheet. What I want to do is dynamically return

a 2D table that summarises any combination of that data (including

aggregating and filtering). I have successfully implemented this using the

following formula:

=SUM((INDIRECT($D$6&"!"&$B$3&"2:"&$B$3&"35393")=$D18)*

(INDIRECT($D$6&"!"&$B$2&"2:"&$B$2&"35393")=F$16)*

IF(ISBLANK($F$4),1,(INDIRECT($D$6&"!"&$B$4&"2:"&$B$4&"35393")=$F$4))*

IF(ISBLANK($F$5),1,(INDIRECT($D$6&"!"&$B$5&"2:"&$B$5&"35393")=$F$5))*

(INDIRECT($D$6&"!J2:J35393")))

D6 contains either 2002 or 1995 (ie the sheet names).

B2:B5 contains the letters of the columns which are being filtered and/or

displayed (dynamically retrieved by simple formulae)

F4:F5 contain possible filters

This formula is copied across a 33 by 33 range (large enough to cover all

possible combinations of rows/columns)

Unfortunately this calculates very slowly - it currently takes about

30s-1min to calculate on a Intel P4 3GHz, with 500Mb Ram, and tends to

recalculate a lot as you need to change other options to set up the table how

you want it.

I have read a few sites on optimising speed of calculations etc, and they

tend to suggest using alternatives to the sum() function but I am not sure

how they apply here given the versatility I am hoping for.

Any advice or suggestions would be very much apprecaited

Regards

Revontulet

Apologies if a similar question has been answered elsewhere in the forum. I have a chart running off of cells which utilize a SUMPRODUCT function to return criteria based on a significant number of rows.

As per below, against three columns the sum product formula returns the sum of three user defined conditions (“Legal“, “Red” and “Project.”):

=SUMPRODUCT((Sheet1!$B2:$B64893="Legal")*(Sheet1!$A2:$A64893="Red")*(Sheet1!$C$2:$C$64893="Project"))

(Formula Inserted via CTRL-Shift-Enter).

Other cells follow the same formula but the first condition (i.e. Legal above) can be either one of four other conditions depending on the cell (either “Legal“, “HR“, “Finance“, “Retail“, as per attached). Although this is working I find this to be incredibly memory intensive and I want to keep the workbook at a reasonable file size.

Could anyone please advise on a alternative method to sum product? I have attached a basic example to clarify.

Best wishes,

Ami

N.B

In my opinion using a pivot table, and then placing a GETPIVOTDATA function into the cells to run chart from is no use to me here. The data will change continually and often one or more of the four conditions will not exist in the data.

A pivot chart is not an option because on Excel 03 a pivot chart does not preserve formats on refresh, and SUMIF(S) are not compatible with 2003.

A colleague of mine maintains a spreadsheet with a worksheet of data for each month labeled "Apr'07", "May'07", "Jun'07", etc. He then has a summary worksheet that gives summaries of the various data fields on the monthly sheets. Currently he has been using the kludgy method of listing each cell to be summed. (e.g. "SUM('Apr'07'!A2,'May'07'!A2,etc.) Since he is dealing with a summary of about 200 rows by 15 columns this is a big pain to maintain.

My first thought was to set up his formulas as a range of sheets rather than naming each; E.g. SUM('Apr''07:Jun''07'!A1)

Then I thought that rather than having to update each formula when he adds a new month, he would be better off having two cells that control the start and end sheet and having then feeding them into the summary formulas using the INDIRECT function.

However, try as I might, I can't seem to get the INDIRECT formula to work.

In the attached example spreadsheet I have set up B1 and B2 to hold the names of the start and end worksheets, respectively. So my attempt at an Indirect Sum formula is the following:

SUM(INDIRECT(" ' "&B1&":"&B2&" ' !A1"))

(Note: spaces added between " and ' for clarity)When I look at the formula evaluator it evaluates to SUM(INDIRECT("'Apr''07:Jun''07'!A1") and then generates a #REF error.

What am I doing wrong here? I'm open to all suggestions of fixes to this method or an alternate method that would achieve the objective of performing calculations on cells across a dynamic range of worksheets.

Thank you!

Ref = Single cell reference in the same column as the function. The function

automatically adds all values from REF through the cell preceding the

function [if D4 = SUMABOVE(D1), the formula sums D13]. This eliminates

issues of inserting rows immediately above the formula row and having to

adjust the formula to include them. Can also work similar to SUBTOTAL

function, where overlapping SUMABOVE functions do not duplicate the amounts

added.

Alternatively, REF can be in a different column and a block will be added,

i.e., if D4 = SUMABOVE(A1), the result is SUM(A13).

Similarly: SUMLEFT, SUMRIGHT, SUMDOWN

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

This post is a suggestion for Microsoft, and Microsoft responds to the

suggestions with the most votes. To vote for this suggestion, click the "I

Agree" button in the message pane. If you do not see the button, follow this

link to open the suggestion in the Microsoft Web-based Newsreader and then

click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

by month (e.g. Col A = January Budget, Col B = January Actual, Col C = Feb

Budget...).

I need to add a year to date sum, for both budget and actual, which will

need to fluctuate to accomodate the passage of time (e.g. on March 1 include

Jan & Feb, on April 1 include Jan Feb and March).

Is there a way to make SUMIF work horizontally (like an HLOOKUP)? Then I

could just add a hidden row with the date I want that column included and

make the SUMIF criteria based on the relationship between the TODAY function

and that date. Any other ideas on how to approach this?

Thank you!

Erin

Ref = Single cell reference in the same column as the function. The function

automatically adds all values from REF through the cell preceding the

function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates

issues of inserting rows immediately above the formula row and having to

adjust the formula to include them. Can also work similar to SUBTOTAL

function, where overlapping SUMABOVE functions do not duplicate the amounts

added.

Alternatively, REF can be in a different column and a block will be added,

i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3).

Similarly: SUMLEFT, SUMRIGHT, SUMDOWN

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

This post is a suggestion for Microsoft, and Microsoft responds to the

suggestions with the most votes. To vote for this suggestion, click the "I

Agree" button in the message pane. If you do not see the button, follow this

link to open the suggestion in the Microsoft Web-based Newsreader and then

click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

Need to change the format of the formula from =SumVisCells(A3:H3) to =SumVisCells(A2,C2,E2,G2) as i need to pickup alternative visible columns with sumviscells not takinig into account hidden columns. Thanks

Function SumVisCells(Cells_To_Sum As Object) Application.Volatile For Each cell In Cells_To_Sum If cell.Rows.Hidden = False Then If cell.Columns.Hidden = False Then Total = Total + cell.Value End If End If Next SumVisCells = Total End Function

by month (e.g. Col A = January Budget, Col B = January Actual, Col C = Feb

Budget...).

I need to add a year to date sum, for both budget and actual, which will

need to fluctuate to accomodate the passage of time (e.g. on March 1 include

Jan & Feb, on April 1 include Jan Feb and March).

Is there a way to make SUMIF work horizontally (like an HLOOKUP)? Then I

could just add a hidden row with the date I want that column included and

make the SUMIF criteria based on the relationship between the TODAY function

and that date. Any other ideas on how to approach this?

Thank you!

Erin

I have cells in one worksheet which contain formulas referring to cells in a PivotTable in another worksheet. I've used the GETPIVOTDATA function to set the formulas, and they look like this:

=GETPIVOTDATA("Sum",OtherSheet!$A$3,"Class","RCO","Date'",DATE(2007,7,1))

However, the PivotTable may be moved to another location within the "OtherSheet" worksheet, and so I'd like for all my formulas to continue to work if the table is moved. Ideally I'd like to replace the "$A$3" reference in the formula above to one utilizing the name of the PivotTable. I've tried variations of formulas like:

=GETPIVOTDATA("Sum'",OtherSheet!PivotTables("NameOfPT").tablerange2,"Class'","RCO","Date'",DATE(2007 ,7,1))

I can't find anything to work without getting some kind of #NAME? error or similar.

Does anyone know how to refer to a PivotTable location by name with the GETPIVOTDATA function?

An alternative idea is to use a cell reference without the GETPIVOTDATA function. Since I know exactly which absolute column to reference on the other sheet, if I could use some function or property of the PivotTable that returns the absolute row number for a specific item (e.g. "RCO" in the above example) in the rowrange, then I could use that row and the column I already know as the reference in the cell formula. If anyone knows how to do that, I would appreciate that as well!

Many thanks!

I have the following problem:

I used the DSUM formula to sum the costs between a range of dates (condition).

I have the dates in rows (records) and the costs for each date in columns(values).

Now I have changed the dates to columns (records) and the costs to rows (values).

The conditions remains the same >= Date1 and <=Date2.

What I would like to know if there is an alternative to DSUM that would look progressively at the rows and not at the columns for the Values and Rows.

Sort of I have the DSUM as a VLOOKUP and would need a DSUM for HLOOKUP.

I know they are different functions but it is just to give you the idea of what I need.

I would very much appreciate it if anyone could help me with this issue.

In worksheet function form the INDEX function can for example do the following (all context aside):

a) return single Values

note: here the above acts like INDIRECT in so far as if row 4 is deleted the above would still return contents of the "new" A4 and not errorLet's now assume we want to Sum the contents of the 2nd "Column" of our 2d Array without having to iterate each "item", how can we do this ?

(we are for sake of simplicity assuming Col A is not deleted in above example)

b) return Array of Values for use in conjunction with other functionsc) be used to create Ranges, eg for use as Dynamic Named Rangeswhich all in all makes it incredibly flexible and it does all of this whilst being non-volatile (post XL97) unlike equivalent function types (eg OFFSET, INDIRECT etc...)Name: _PivotData RefersTo: =$A$1:INDEX($A:$IV,COUNTA($A:$A),COUNTA($1:$1))

However, further to it's "native" flexibility it's also very useful in VBA specifically when working with Arrays.

Let's assume we have a numeric 2d Array which for sake of "alternatives" is not sourced from a Range object.

Using a very basic (and admittedly unrealistic) set up along the lines of:Public Sub Example() Dim lngY As Long, lngX As Long Dim MyArray(1 To 50000, 1 To 4) As Double For lngY = LBound(MyArray, 1) To UBound(MyArray, 1) Step 1 For lngX = LBound(MyArray, 2) To UBound(MyArray, 2) Step 1 MyArray(lngY, lngX) = Rnd Next lngX Next lngY End Sub

INDEX offers us a very simple method:

The same can be applied for a given "row" - say the 10th row of our ArrayThis is very handy but what if we wanted to sum an area within our Array - say from "row" 50 to "row" 15000 (inclusive) whilst omitting "columns" 1 & 4 ?but how can we reflect the same in VBA using INDEX ?

In terms of the native function and a range this is relatively straightfoward, if we assume our 50000 x 4 matrix is A1:D50000 then:=SUM(INDEX(A:D,50,2):INDEX(A:D,15000,3)) or =SUM(INDEX(B:B,50):INDEX(C:C,15000))

Previously I thought this was not possible... then along came our very own lecxe with this very clever trick:note: you could use shorthand for the Evaluate if preferred - ie [row(50:15000)]

That's pretty darn clever !!!

Better yet this technique can handle non contiguous parts of the source array - say we wanted the same rows but to sum columns 1 & 4 rather than 2 & 3:that's super clever !!! especially when you consider the "native" INDEX equivalent:

Better still (is this even possible?!) pending actions we could even revert the ordering of the "columns"

So let's now assume that we have a 5th Column in our Array which is populated with strings which may or may not repeat ("apple", "banana" etc)

We now want to match "apple" in between "rows" 50 and 15000 in our Array and return the value associated with that match from "Column" 3

Using the above technique we could create a right to left VLOOKUP using this sub array technique

If you feel you must reply to the above (not expected) please note the following:

a) the above is not a question

b) none of the techniques outlined above are being advocated as "best practice"

c) the set ups above are entirely hypothetical Following on from the above:

though there are countless alternative methods for all of the above they it is not the intention to discuss them here - this post is merely to demonstrate lecxe's Evaluate w/INDEX trick.

The post in which this trick was first brought to my attention resides a) on another Excel Message Board and b) within a restricted forum of that Message Board so at this stage I can not provide a direct link unfortunately.

i'm new so apologize for this.

here's my problem (i cant post a file due to some issues i have)

but i have approximately 50 sheets that i want to sum. i have list down the sheet names and added a list called say (50sheets).

at the moment i'm using this function =SUMPRODUCT(SUMIF(INDIRECT(ADDRESS(5,1,1,1,INDIRECT("50sheets"))),$A14,INDIRECT(ADDRESS(5,COLUMN(),1 ,1,INDIRECT("50sheets"))))) and whilst it works fine if all column position is the same as the summary sheet, i have a problem if the column number in the individual sheets are different. They do have the same row though

i then used a different approach using this function =SUMPRODUCT(SUMIFS(INDIRECT(ADDRESS(5,COLUMN(),1,1,INDIRECT("50sheets"))),INDIRECT(ADDRESS(5,1,1,1,I NDIRECT("50sheets"))),$A14,INDIRECT(ADDRESS(4,COLUMN(),1,1,INDIRECT("50sheets"))),2012)) but this seems also to only cater to the same column number (whilst 2 variables), i.e. one with the year 2012, and one with the $a14 which is a word). The issue is that the year 2012 is actually in different columns in those 50 sheets.

Can somebody help ? should i disregard the address formula?

I've attached an example. In the example, the only time the #DIV/0 appears is when "Y" appears in the Commit and In-House column for all rows. This is the condition I'm trying to suppress the #DIV/0 error for.

All the formula's I'm using are Array Formula, as are all my attempts to suppress the error.

Original Formula

=SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",C3:C5)/SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",B3:B5)))))))

(A2=0,"",A1/A2) Method

=IF(SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",C3:C5)/SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",B3:B5)))))))=0,"",SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",C3:C5)/SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",B3:B5))))))))

I looked at another possible solution that Dave posted that uses Custom Formatting on a per cell basis, but I can't seem to get that working either.

Originally Posted by Dave To hide zeros cell-by-cell use a Custom Number Format like 0.00;-0.00; where 0.00 is desired format for non zeros. Note the use of -0.00 for negatives. Any alternatives or suggestions are greatly appreciated.

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