Arrays with INDEX formula

[2077delta]

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.

 

[Aladin Akyurek]

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

 

[2077delta]

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

 

[Aladin Akyurek]

Control+shift+enter, not just enter...

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

=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

 

[2077delta]

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.

 

[Aladin Akyurek]

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.