Arrays with INDEX formula

2077delta

Board Regular
Joined
Feb 17, 2002
Messages
245
On Sheet 1 have two columns of data. In column A are card numbers and in column B are gallons. On sheet 2 I have a table of the card numbers (in no particular order) and whether they are for gas or diesel. I can use the combination INDEX and MATCH inside one formula to determine whether the cards on Sheet 1 are gas or diesel, but how do I embed this in an array formula to summarize the gallons on Sheet 1 by gas and diesel? Let's also assume that other factors prevent me from adding a column on Sheet 1 to indicate whether each card listed is gas or diesel. Here is the formula I'm currently using, but it returns a total for all the cards, not just for the ones identified as gas.

{=SUM((INDEX(cardnoindex,MATCH(TEXT($A$8:$A$35,"#"),cardnoindexcard,0),2)="Gas")*($B$8:$B$35))}

In this formula "cardnoindex" is the range name on Sheet 2 of the table of card numbers and type of fuel and "cardnoindexcard" is the range name of just the range of card numbers that make up "cardnoindex". You will also notice that I am converting the card numbers on Sheet 1 to text since they are entered as values on Sheet 1 and as text on Sheet 2. This is just one more of the idiosyncrasies of this file.

Any help would be greatly appreciated.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Would you specify the problem in terms of real ranges instead of named ranges?

Something like...

Sheet X, range A2:A100 houses card numbers...
Sheet X, range B2:B100 houses gallons...
Sheet Y, range ....

Needed: A sum of ...
 
Upvote 0

2077delta

Board Regular
Joined
Feb 17, 2002
Messages
245
Assume the following:
Sheet 1
Range A1:A25 = card numbers
Range B1:B25 = gallons
Sheet 2
Range A1:A8 = card numbers
Range B1:B8 = type of fuel (gas or diesel)

My formula would look like
{SUM((INDEX('Sheet2'!A1:B8,MATCH(TEXT($A$1:$A$25,"#"),'Sheet2'!A1:A8,0),2)="Gas")*($B$1:$B$25))}

Result would be the summary of gallons on Sheet 1 for "Gas".

Hope this helps
 
Upvote 0

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Control+shift+enter, not just enter...
Code:
=SUM(
   IF(ISNUMBER(MATCH(Sheet1!$A$1:$A$25,
        IF(Sheet2!$B$1:$B$8="gas",Sheet2!$A$1:$A$8,""),0)),
      Sheet1!$B$1:$B$25))

which yields a summary total for "gas".

If resolving the issue of text-numbers vs numbers must be done in the above formula...

Code:
=SUM(
   IF(ISNUMBER(MATCH(Sheet1!$A$1:$A$25&"",
        IF(Sheet2!$B$1:$B$8="gas",Sheet2!$A$1:$A$8,""),0)),
      Sheet1!$B$1:$B$25))

Assume the following:
Sheet 1
Range A1:A25 = card numbers
Range B1:B25 = gallons
Sheet 2
Range A1:A8 = card numbers
Range B1:B8 = type of fuel (gas or diesel)

My formula would look like
{SUM((INDEX('Sheet2'!A1:B8,MATCH(TEXT($A$1:$A$25,"#"),'Sheet2'!A1:A8,0),2)="Gas")*($B$1:$B$25))}

Result would be the summary of gallons on Sheet 1 for "Gas".

Hope this helps
 
Last edited:
Upvote 0

2077delta

Board Regular
Joined
Feb 17, 2002
Messages
245
Thanks a ton. This works great, much simpler than what I was proposing. Array formulas are great, I just wish I had the same command of them as you and the other wizards out there. Is there any reference that gives a more detail description of the mechanics (and rules) on how these work? The help in Excel is pretty useless when it comes to this type on information.
 
Upvote 0

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Thanks a ton. This works great, much simpler than what I was proposing. Array formulas are great, I just wish I had the same command of them as you and the other wizards out there. Is there any reference that gives a more detail description of the mechanics (and rules) on how these work? The help in Excel is pretty useless when it comes to this type on information.

You are welcome and thanks for the feedback.

The best we can do in order to grasp the mechanics of (such) formulas is, practically speaking, to study the formulas which solve clearly delineated problems...

The mechanics of the formula in question can be construed as...

The problem:

Total the gallon if a target card number is in the set of card numbers which are associated with the "gas" type of fuel.

Calculations needed:

(A) Determine the subset of card numbers that are gas card numbers:

==> IF(Sheet2!$B$1:$B$8="gas",Sheet2!$A$1:$A$8,"")

(B) Determine the target card numbers which are in A.

==> ISNUMBER(MATCH(Sheet1!$A$1:$A$25,A,0))

(C) Determine the gallon total for the target card numbers that are in A. That is, Total gallons if B=TRUE or SUM(IF(B,Gallons). Substitutions would then yield a runnable Excel formula.

Hope such a construal helps.
 
Upvote 0

Forum statistics

Threads
1,190,957
Messages
5,983,837
Members
439,866
Latest member
jh3268

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top