You are here: HomeExcelTutorials

# SUM a variable range with INDEX and MATCH

## SUM a variable range with INDEX and MATCH

Price: FREE

The formula below works perfectly to find the number being matched but I need it to also calculate the SUM of a range with the last cell of that range being the matched cell, in this case TeamStats!F13, and the first cell of the range to be TeamStats!F5.
=IF(C6="","",IF(ISERROR(INDEX(TeamStats!F:F,MATCH(\$E\$1&C6,TeamStats!B\$1:B\$1271&TeamStats!A\$1:A\$1271, 0)),"",INDEX(TeamStats!F:F,MATCH(\$E\$1&C6,TeamStats!B\$1:B\$1271&TeamStats!A\$1:A\$1271,0))

Visit publisher's web-site: SUM a variable range with INDEX and MATCH

Category and Tags: ExcelTutorials :

## Related Tutorials

### How to make a variable range in formula?

Free: Yes
Tags: Excel ›

I have a formula like this
=INDEX(A10:G14,MATCH("B1",A10:A14,0),MATCH("m",A10:G10,0)+1)
to return the value beside "m" of a desired row "B1". A10:G10 is the range of the row of "B1" and "B1" is one of the entry in a simple database.
As the row of "B1" is a variable so the range is also a variable. Unfortunately ADDRESS cannot be nested in formula here.
Anyone has better idea?

### Sum an Index Match

Free: Yes
Tags: Excel ›

I'm using the formula =INDEX(E14:E20,MATCH(E7,F14:F20,FALSE),1) which is working to bring back a result however I need to bring back a sum
I have the match word with a \$ value in the adjacent cell and need to sum multiple \$ values together based on the word matching

### Using a Index and Match with sum bold VBA function

Free: Yes
Tags: Excel ›

I am quite new to VBA, but I was able to find the macro to sum bold cells in a given range, but I am trying to lookup a certain value in another range and if that value is found then sum bold cells within another range. I have tried several different combinations of using if(xx=xx(sumbold( , or vlookup, index and match and,using VLOOKUP() nested inside the ISERROR() function. if result is TRUE, run the macro , etc but I cannot get the formula to work.
For example:
=ISERROR(VLOOKUP(A42,MIDs!A4:A16531,sumbold(MIDs!\$L\$4:\$L\$6235)+sumifbold(MIDs!\$N\$4:\$N\$6235),0))
I am hoping that someone with more experience might be able to help. If needed I will post the spreadsheet as well.
Thanks to anyone that can

### Sum Variable Range

Free: Yes
Tags: Excel ›

Assuming the given data (in the image at the link below), is there a formula that I can put into D3 that will sum a given number of cells based on the value in cell D2? I can't do this with if statements because my actual data has 20 columns, and dozens of rows. (The formulas would actually be in the same row as the data, and it would be summing part of the row. and this would be repeated on each row for alternate data.)
Basically if I put 2 in I want it to sum(a6:b6), if I have 5 in D2, then I want D3 to sum(a6:e6).
I know I can figure this out easy enough with VBA, but I was wondering if there's a way to do it with a formula?

### How to make variable range in excel (based on cell value)?

Free: Yes
Tags: Excel ›

I've been trying many things and browsing web with Google (including many threads in this forum), but haven't found an answer.
So, I basically want define range that varies based on another cell value. Simple example below
I have values in cells A1:A30. I want to sum a range of 10 values: e.g. sum(A1:A10). The exact starting point of this range would need to be defined by another cell, let's say B1. Two examples:
if B1 = 2, the sum range would become: sum(A3:A12)
if B1 = 5, the sum range would become: sum(A6:A15)
i.e., B1 value is added to the row number. How is this done without any complex coding/macros?
Kalle
ps. I plan to use this dynamic range in functions (like sum) and in charts (to easily change the "rolling window" of source data based on a one cell value)

## Related Applications & Scripts

### Explore Variables

Free: No
Tags: PHP › Excel Miscellaneous

“Explore” simply allows you to explore your big variables like objects or arrays in clean interface. Just include explore.php and use explore() function to get very interactive interface to explore your variable.
With this source code, you’ll explore your variable with a clean interface, even if they’re huge objects or arrays, you’ll easily navigate on the variable structure.

### create sliders to interactively adjust any variable

Free: No
Tags: Flash › Excel Sliders

Slider.as is an immensely useful development tool. It enables you to create sliders to interactively adjust any variable!
How many particles should be emitted? How much alpha should the button overlay have? How long should the meny fade-in take? With Slider.as you can just drag-n-drop until you get it right!
Sliders supports keyboard input, are drag-&-droppable, can handle negative value spans, can round to a specified decimal place and of course all of their settings can be adjusted in realtime.

### Space Match Memory game - Advanced

Free: No
Tags: Flash › Excel Games

In this space style memory game you have to flip tiles over to match two icons. Flipping over all the tiles ends the game.
This game features a timer and the game remembers your time for the session you are in. The game also has sound.
This is the advanced version of this file. It has the following features:
- PHP compatible saving to text file (.txt)
- ASP compatible saving to tex tfile
- Level select
Easy, Medium and Hard
- Individual scores for each level
- Sorts scores by best time

### Flash Creations: Flash Ultimate Gallery

Free: No
Tags: Flash › Excel Software

This is the perfect tool to share your photographs via the web. Create stunning Flash photo albums from your favorite digital pictures. Simply select the digital photos you care to show, add the captions, and post it to your site....it's that easy! Plus, you can have numerous galleries to organize the photos for your friends and family. And, it is guaranteed to match your website! All the colors are changeable by you. The headline is changeable, the title can change, the outlines, all of it available to change any time.

### Arrays in C

Free: Yes
Tags: C++ › Excel Getting Started

Array is a collection of same type elements under the same variable identifier referenced by index number. Arrays are widely used within programming for different purposes such as sorting, searching and etc. Arrays allow you to store a group of data of a single type. Arrays are efficient and useful for performing operations . You can use them to store a set of high scores in a video game, a 2 dimensional map layout, or store the coordinates of a multi-dimensional matrix for linear algebra calculations.