SumIf across columns

I'm fairly new with Excel and I cannot figure out how to accomplish my task.

I have two sets of numbers (see below)


A................B................ C............. D............. E..............F..............G

Unit............15 days.........30 days......Unit........15days....... 30days
iron............ 7..................3............... pan.........6................. 2
waffle...........2.................. 6...............cup..........2................ 9
onion...........1...................8...............iron..........6.................17

I need to find a formula will look for the unit specified in column A and combine corresponding values in B & F. (So if when it finds iron in E4 it will add B2 and F3)

Hopefully I explained this well enough, if you need more information please let me know.

Thanks in advance!

Lithium, thanks for changing the title.

I don't quite understand your requirement, maybe because the layout above is not easy to grab.
When "iron" is found in E4, you want to add B2 and F3. Can you explain why these cells? What is their connection with "iron" in E4?

Maybe you could post a data sample in a workbook and explain in context?

try this

B2 =INDEX(E:E,MATCH($A2,$D:$D,0))+INDEX(H:H,MATCH($A2,$G:$G,0))

copy across to C2, then copy B and C down.

hth

yup.

=IF(ISNA(MATCH($A2,$D:$D,0)),0,INDEX(E:E,MATCH($A2,$D:$D,0)))+IF(ISNA(MATCH($A2,$G:$G,0)),0,INDEX(H:H,MATCH($A2,$G:$G,0)))

the forum software puts a space into long formulas. I should have put it in code tags.

Please Login or Register  to view this content.

if you look closely above, you'll see a space in the last part of the formula
INDEX(H: H,MATCH($A2,$G:$G,0)))
here at H: H
that's where it bombs.

INDEX is fast and powerful.

thanks for the rep

FWIW I think you will find a SUMIF will work in this instance, eg:

B2: =SUMIF($D$2:$G$4,$A2,E$2:H$4)
copied across matrix B2:C5

this works because the pattern over the 2 data tables is consistent and the criteria values are non-numeric (ie sum values and criteria values are mutually exclusive)

You see, Lithium, the water depth is relative. We have some deep sea divers here!

Thanks, DO, for taking this to the next level.