Compare values in columns, then add a row of numbers 3

[Hasit]

I have two seperate data blocks, both with a number of columns. The first data source has a column with town names.

The second data block has 4 columns, the first has town names, the remaining three columns has numbers (there are about 200 rows in total in each data block).

Both these data blocks are in the same worksheet in the same workbook.

What I am trying to do is to compare the town names from each data block (they may or may not appear in the same row). If the name matches, then the corresponding row in the second data block where there was a match should be added to give a total figure.

I have tried a number of "IF", "SUMIF", conditional arguments etc, but I cannot get it to work.

Pls help!

To help visualise the problem:

Datablock 1 Datablock 2 Results
A B C A B C D
1 Town1 Town1 4 7 9 20
2 Town3 Town2 6 1 5 0
3 Town4 Town7 1 6 9 16
4 Town5 Town9 4 4 6 0
5 Town7 Town4 7 5 9 21

So the above would yield the results as shown:

 

[JVFriederick]

Resend your last part.

This problem is easy to do with Matrix Arrays.

 

[ETID]

IS THIS YOUR ANSWER?


IN COLUMN H ROW 2,THE FORMULA READS, =IF(COUNTIF(A:A,B2)>0,SUM(C2:E2),"&quot



A B C D E F G H
Town1 Town1 4 7 9 20 20
Town3 Town2 6 1 5 0
Town4 Town7 1 6 9 16 16
Town5 Town9 4 4 6 0
Town7 Town4 7 5 9 21 21

 

[ETID]

OOPS. ...Sorry the column letters did not line up :-(

 

[JVFriederick]

No, it wasn't my answer. Just leting you know one is on the way. Hang on for a few minutes.

 

[JVFriederick]

In cell B1 next to Datablock1, write the following equation (assume 200 rows of data) :

=SUM((D$1$200=A1)*H$1:H$200)
Now press Ctrl+Shift+Enter so the equation looks like this :
{=SUM((D$1$200=A1)*H$1:H$200)}

You can now copy the equation in B1 down through B5.

If you want the formula to add the numbers together in all 3 columns, use the following :
{=SUM((D$1$200=A1)*E$1:G$200)}

If you want multiple criteria, such as values in column F greater than 5, use the following equation :

{=SUM((D$1$200=A1)*(F$1:F$200>5)*G$1:G$200)}

There is also a FAQ on matrix arrays that explains how this little gem works in more detail.

 

[ETID]

When I Asked if this was your answer, I was refering to Hasit....the formula does work, why do you need an array? or am I missing something?

 

[Hasit]

ETID: By jove, I think you have cracked it! Absolutlely storming. Thanks!

 

[Hasit]

JVFriederick: Thanks for your reply too. I tried both solutions and they both work. Yours is more elegant, in that you can perform multiple checks before taking an action, whilst ETID's is specific to my question alone.

I have read your FAQ and its excellent. Thanks again!

 

[JVFriederick]

 

[DaleWatson123321]

To "Hasit"

Pssssssssst... In checking your "profile", I see that you are relatively new to Tek-Tips, and therefore not yet aware of the "complete" method of "showing appreciation" - which is to give the contributors a "STAR".

This is done by simly clicking on the "Let ______ know this post was helpful". These STARS not only show FULL appreciation, but ALSO serve as "beacons" for other Tek-Tips users who are looking for solutions to similar problems. It also confirms to other would-be contributors that the problem has been resolved.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Hasit]

Dale,

Thanks for the tip, I will be adding several stars on this one and future questions too I am sure.

Hasit.