# Altering a match/index formula

#### KollBrian

##### Board Regular
Hey all, I need to alter an index/match formula to work with rows instead of columns.

Here is the original formula:

below are the 2 examples of where I need to put the new formula and where it needs to look.
Month End Compilation Report.xls
ABCD
46
47Benefiel,Krissa110.0%
48Bowker,Dave110.0%
49Briceno,Alma110.0%
50Crooks,Holly110.0%
Printable Report
Month End Compilation Report.xls
ABCDEFG
81AbricenoAbricenoAcontrerasAcontrerasAoverbyAoverby
82PUDelPUDelPUDel
83158
84255
85347
86Totals6554785

So, what I am trying to do is match the name in column "I" (the abreviated name) and the determining code (i.e. "pu" or "del") with the name and determining code in another worksheet, then give the total at the bottom of the sheet.

So that if the formula is for the loads picking up the formula looks for abriceno, then pu, then gives the total at the bottom of that column.....???

I am going to start hammering at this trying to see if I can figure it out, maybe a dual match... not sure yet, but if any of you have any thoughts, let me know.

bk

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Just an update, I am tampering with this formula at the moment, it at least is taking, but I get the #Value error so something is still not right.

Wow, it is rare when I can stump this entire group.

Just another update, so far I have not been able to get a formula to give me the desired results. The updated formula I was trying, would finally give me a result, but a result of "1", not the actual number.

So its back to the design board, ugh....

bk

When using SUMPRODUCT, the ranges must be the same size

Max,

I dont understand, the ranges need to be the same size?

in this example I am trying to search range A (a2:dd2) then find the qualifier (pu or del) one row below, then give the result of the sum at the bottom (row 74) of the column.

How can I make the ranges the same size?

bk
This message was edited by KollBrian on 2002-09-05 00:43

From the help files:

The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.

Max, Everyone,

Ok, what if I give up on the trying to search/match 2 criterium, and simply go with something like "=SUMPRODUCT(('CSR Load Count'!B1:DD1=\$I47)*(\$B73:DD73>0))". I have made the 2 ranges the same size, but still dont get the total down below....

This presumes I change the persons name to name1, and name2 instead of a1=name, b1=pu, a2=name, b2=Del.

I am starting to get scrambled I think. Maybe I have finally reached the limit of excel....

bk

Brian

Are you trying to sum the totals of PU and DEL or just count the PU and DEL? If you're summing you're missing an argument to sum what range.

I am trying to either sum the column or copy the result of row 74 for that column (which is a sum formula).

The hard part is i need it to look in row 1 for the name (there are 2 identical listings per person) then when it finds the name I need it to look in row 2 for the "qualifier" (a word either "pu" or "del") and then give me the sum of that particular column.

That is the hard part, I can match when its in column format but I cant seem to do it in row.

For instance if the names were in column a, the qaulifiers in column b, and the quantity was in c:dd, I think I would have no trouble.

Its the fact that the "if" statements are in rows that seems to be causing the problem.

bk

Brian,

Do a search of SUMPRODUCT and Aladin Akyurek as the author.

Replies
4
Views
219
Replies
1
Views
139
Replies
1
Views
596
Replies
2
Views
131
Replies
0
Views
363

1,218,539
Messages
6,143,082
Members
450,461
Latest member
Bosavon

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

### Which adblocker are you using?

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

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